Here are general trigger functions provided as workable examples

of using SPI and triggers. "General" means that functions may be

used for defining triggers for any tables but you have to specify

table/field names (as described below) while creating a trigger.

1. refint.c - functions for implementing referential integrity.

check_primary_key () is to used for foreign keys of a table.

You are to create trigger (BEFORE INSERT OR UPDATE) using this

function on a table referencing another table. You are to specify

as function arguments: triggered table column names which correspond

to foreign key, referenced table name and column names in referenced

table which correspond to primary/unique key.

You may create as many triggers as you need - one trigger for

one reference.

check_foreign_key () is to used for primary/unique keys of a table.

You are to create trigger (BEFORE DELETE OR UPDATE) using this

function on a table referenced by another table(s). You are to specify

as function arguments: number of references for which function has to

performe checking, action if referencing key found ('cascade' - to delete

corresponding foreign key, 'restrict' - to abort transaction if foreign keys

exist, 'setnull' - to set foreign key referencing primary/unique key

being deleted to null), triggered table column names which correspond

to primary/unique key, referencing table name and column names corresponding

to foreign key (, ... - as many referencing tables/keys as specified

by first argument).

Note, that NOT NULL constraint and unique index have to be defined by

youself.

There are examples in refint.example and regression tests

(sql/triggers.sql).

To CREATE FUNCTIONs use refint.sql (will be made by gmake from

refint.source).

# Excuse me for my bad english. Massimo Lambertini

#

#

# New check foreign key

#

I think that cascade mode is to be considered like that the operation over

main table is to be made also in referenced table .

When i Delete , i must delete from referenced table ,

but when i update , i update referenced table and not delete like unmodified refint.c .

I made a new version of refint.c that when i update it check the type of modified key ( if is a text , char() i

added '') and then create a update query that do the right thing .

For my point of view that policy is helpfull because i do not have in referenced table

loss of information .

In preprocessor subdir i have placed a little utility that from a SQL92 table definition,

it create all trigger for foreign key .

the schema that i use to analyze the problem is this

create table

A

( key int4 not null primary key ,...,

) ;

create table

REFERENCED_B

( key int 4 , ... ,

foreign key ( key ) references A --

);

--

-- Trigger for REFERENCED_B

--

CREATE INDEX I_REFERENCED_B_KEY ON REFERENCED_B ( KEY ) ;

CREATE TRIGGER T_P_REFERENCED_B_A BEFORE INSERT OR UPDATE ON REFERENCED_B FOR EACH ROW

EXECUTE PROCEDURE

check_primary_key('KEY','A','KEY' );

CREATE TRIGGER T_F_D_A_REFERENCED_B BEFORE DELETE ON A FOR EACH ROW

EXECUTE PROCEDURE

check_foreign_key(1,'cascade','KEY','REFERENCED_B ','KEY' );

CREATE TRIGGER T_F_U_A_REFERENCED_B AFTER UPDATE ON A FOR EACH ROW

EXECUTE PROCEDURE

check_foreign_key(1,'cascade','KEY','REFERENCED_B ','KEY' );

-- ********************************

I write TRIGGER T_F_U_A_REFERENCED_B ( AFTER ) and not BEFORE because if i set

BEFORE , when i try to modify ( update ) a key of A , i start a execution of TRIGGER T_P_REFERENCED_B_A

( check_primary_key) before the real modification of key in A , then the execution of ( check_primary_key) return

not ok.

With AFTER Clausole i modify first key of A then a update the value of referenced table REFERENCED_B.

Try also the new_example.sql to view the modified policy.

I wish that my explain of problem is quite clear .

If there is miss understanding ( cause my bad english ) please send email to massimo.lambertini@everex.it

PostgreSQL version (example: PostgreSQL-6.5) : PostgreSQL-6.5

(1999-05-01)

print "__________________________________________________________________"

print "MODULE ADVANCED.PY : ADVANCED POSTGRES SQL COMMANDS TUTORIAL"

print "This module is designed for being imported from python prompt"

print "In order to run the samples included here, first create a connection"

print "using : cnx = advanced.DB(...)"

print "then start the demo with: advanced.demo(cnx)"

print "__________________________________________________________________"

from pgtools import *

from pg import DB

def inherit_demo(pgcnx):

print "-----------------------------"

print "-- Inheritance:"

print "-- a table can inherit from zero or more tables. A query"

print "-- can reference either all rows of a table or all rows "

print "-- of a table plus all of its descendants."

print "-----------------------------"

print

print "-- For example, the capitals table inherits from cities table."

print "-- (It inherits all data fields from cities.)"

print

print "CREATE TABLE cities ("

print " name text,"

print " population float8,"

print " altitude int"

print ")"

print

print "CREATE TABLE capitals ("

print " state varchar(2)"

print ") INHERITS (cities)"

pgcnx.query("CREATE TABLE cities (" \

"name text," \

"population float8," \

"altitude int)")

pgcnx.query("CREATE TABLE capitals (" \

"state varchar(2)) INHERITS (cities)")

wait_key()

print

print "-- now, let's populate the tables"

print

print "INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63)"

print "INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174)"

print "INSERT INTO cities VALUES ('Mariposa', 1200, 1953)"

print

print "INSERT INTO capitals VALUES ('Sacramento', 3.694E+5, 30, 'CA')"

print "INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI')"

print

pgcnx.query(

"INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63)")

pgcnx.query(

"INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174)")

pgcnx.query(

"INSERT INTO cities VALUES ('Mariposa', 1200, 1953)")

pgcnx.query("INSERT INTO capitals" \

" VALUES ('Sacramento', 3.694E+5, 30, 'CA')")

pgcnx.query("INSERT INTO capitals" \

" VALUES ('Madison', 1.913E+5, 845, 'WI')")

print

print "SELECT * FROM cities"

print pgcnx.query("SELECT * FROM cities")

print "SELECT * FROM capitals"

print pgcnx.query("SELECT * FROM capitals")

print

print "-- like before, a regular query references rows of the base"

print "-- table only"

print

print "SELECT name, altitude"

print "FROM cities"

print "WHERE altitude > 500;"

print pgcnx.query("SELECT name, altitude " \

"FROM cities " \

"WHERE altitude > 500")

print

print "-- on the other hand, you can find all cities, including "

print "-- capitals, that are located at an altitude of 500 'ft "

print "-- or higher by:"

print

print "SELECT c.name, c.altitude"

print "FROM cities* c"

print "WHERE c.altitude > 500"

print pgcnx.query("SELECT c.name, c.altitude " \

"FROM cities* c " \

"WHERE c.altitude > 500")

def array_demo(pgcnx):

print "----------------------"

print "-- Arrays:"

print "-- attributes can be arrays of base types or user-defined "

print "-- types"

print "----------------------"

print

print "CREATE TABLE sal_emp ("

print " name text,"

print " pay_by_quarter int4[],"

print " schedule text[][]"

print ")"

pgcnx.query("CREATE TABLE sal_emp (" \

"name text," \

"pay_by_quarter int4[]," \

"schedule text[][])")

wait_key()

print

print "-- insert instances with array attributes. "

print " Note the use of braces"

print

print "INSERT INTO sal_emp VALUES ("

print " 'Bill',"

print " '{10000,10000,10000,10000}',"

print " '{{\"meeting\", \"lunch\"}, {}}')"

print

print "INSERT INTO sal_emp VALUES ("

print " 'Carol',"

print " '{20000,25000,25000,25000}',"

print " '{{\"talk\", \"consult\"}, {\"meeting\"}}')"

print

pgcnx.query("INSERT INTO sal_emp VALUES (" \

"'Bill', '{10000,10000,10000,10000}'," \

"'{{\"meeting\", \"lunch\"}, {}}')")

pgcnx.query("INSERT INTO sal_emp VALUES (" \

"'Carol', '{20000,25000,25000,25000}'," \

"'{{\"talk\", \"consult\"}, {\"meeting\"}}')")

wait_key()

print

print "----------------------"

print "-- queries on array attributes"

print "----------------------"

print

print "SELECT name FROM sal_emp WHERE"

print " sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2]"

print

print pgcnx.query("SELECT name FROM sal_emp WHERE " \

"sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2]")

print

print "-- retrieve third quarter pay of all employees"

print

print "SELECT sal_emp.pay_by_quarter[3] FROM sal_emp"

print

print pgcnx.query("SELECT sal_emp.pay_by_quarter[3] FROM sal_emp")

print

print "-- select subarrays"

print

print "SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE "

print " sal_emp.name = 'Bill'"

print pgcnx.query("SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE " \

"sal_emp.name = 'Bill'")

def demo_cleanup(pgcnx):

print "-- clean up (you must remove the children first)"

print "DROP TABLE sal_emp"

print "DROP TABLE capitals"

print "DROP TABLE cities;"

pgcnx.query("DROP TABLE sal_emp")

pgcnx.query("DROP TABLE capitals")

pgcnx.query("DROP TABLE cities")

def demo(pgcnx):

inherit_demo(pgcnx)

array_demo(pgcnx)

demo_cleanup(pgcnx)