elog(ERROR,"check_primary_key: must be fired for row");

if (!TRIGGER_FIRED_AFTER(trigdata->tg_event))

elog(ERROR,"check_primary_key: must be fired by AFTER trigger");

if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))

tuple=trigdata->tg_trigtuple;

elog(ERROR,"check_foreign_key: cannot process INSERT events");

if (!TRIGGER_FIRED_AFTER(trigdata->tg_event))

elog(ERROR,"check_foreign_key: must be fired by AFTER trigger");

trigtuple=trigdata->tg_trigtuple;

kvals= (Datum*)palloc(nkeys*sizeof(Datum));

snprintf(ident,sizeof(ident),"%s$%u",trigger->tgname,rel->rd_id);

snprintf(ident,sizeof(ident),"%s$%u$%c",trigger->tgname,rel->rd_id,is_update ?'U' :'D');

plan=find_plan(ident,&FPlans,&nFPlans);

relname=args[0];

snprintf(ident,sizeof(ident),"%s$%u",trigger->tgname,rel->rd_id);

plan=find_plan(ident,&FPlans,&nFPlans);

ret=SPI_execp(plan->splan[r],kvals,NULL,tcount);

{

constchar*operation;

if (action=='c')

operation=is_update ?"updated" :"deleted";

operation="set to null";

elog(NOTICE,"%s: "UINT64_FORMAT" tuple(s) of %s are %s",

trigger->tgname,SPI_processed,relname,

(action=='c') ?"deleted" :"set to null");

trigger->tgname,SPI_processed,relname,operation);

}

args+=nkeys+1;/* to the next relation */

<para>

<function>check_primary_key()</function> checks the referencing table.

To use, createa <literal>BEFORE INSERT OR UPDATE</literal> trigger using this

To use, createan <literal>AFTER INSERT OR UPDATE</literal> trigger using this

function on a table referencing another table. Specify as the trigger

arguments: the referencing table's column name(s) which form the foreign

key, the referenced table name, and the column names in the referenced table

<para>

<function>check_foreign_key()</function> checks the referenced table.

To use, createa <literal>BEFORE DELETE OR UPDATE</literal> trigger using this

To use, createan <literal>AFTER DELETE OR UPDATE</literal> trigger using this

function on a table referenced by other table(s). Specify as the trigger

arguments: the number of referencing tables for which the function has to

perform checking, the action if a referencing key is found

unique index.

</para>

<para>

Note that if these triggers are executed from

another <literal>BEFORE</literal> trigger, they can fail unexpectedly. For

example, if a user inserts row1 and then the <literal>BEFORE</literal>

trigger inserts row2 and calls a trigger with the

<function>check_foreign_key()</function>,

the <function>check_foreign_key()</function>

function will not see row1 and will fail.

</para>

<para>

There are examples in <filename>refint.example</filename>.

</para>

-- (fkey3)--> fkeys2 (pkey23)

--

create trigger check_fkeys_pkey_exist

before insert or update on fkeys

after insert or update on fkeys

for each row

execute function

check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');

create trigger check_fkeys_pkey2_exist

before insert or update on fkeys

after insert or update on fkeys

for each row

execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23');

--

-- For fkeys2:

-- (fkey21, fkey22)--> pkeys (pkey1, pkey2)

--

create trigger check_fkeys2_pkey_exist

before insert or update on fkeys2

after insert or update on fkeys2

for each row

execute procedure

check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');

-- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)

--

create trigger check_pkeys_fkey_cascade

before delete or update on pkeys

after delete or update on pkeys

for each row

execute procedure

check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',

-- fkeys (fkey3)

--

create trigger check_fkeys2_fkey_restrict

before delete or update on fkeys2

after delete or update on fkeys2

for each row

execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');

insert into fkeys2 values (10, '1', 1);

NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted

NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted

update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';

NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted

ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys"

CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "

NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are updated

NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are updated

update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';

NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted

NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted

ERROR:duplicate key value violates unique constraint "pkeys_i"

DETAIL:Key (pkey1, pkey2)=(7, 70) already exists.

SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,

action_order, action_condition, action_orientation, action_timing,

action_reference_old_table, action_reference_new_table

ORDER BY trigger_name COLLATE "C", 2;

trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table

----------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+----------------------------

check_fkeys2_fkey_restrict | DELETE | public | fkeys2 | 1 | | ROW |BEFORE | |

check_fkeys2_fkey_restrict | UPDATE | public | fkeys2 | 1 | | ROW |BEFORE | |

check_fkeys2_pkey_exist | INSERT | public | fkeys2 | 1 | | ROW |BEFORE | |

check_fkeys2_pkey_exist | UPDATE | public | fkeys2 | 2 | | ROW |BEFORE | |

check_fkeys_pkey2_exist | INSERT | public | fkeys | 1 | | ROW |BEFORE | |

check_fkeys_pkey2_exist | UPDATE | public | fkeys | 1 | | ROW |BEFORE | |

check_fkeys_pkey_exist | INSERT | public | fkeys | 2 | | ROW |BEFORE | |

check_fkeys_pkey_exist | UPDATE | public | fkeys | 2 | | ROW |BEFORE | |

check_pkeys_fkey_cascade | DELETE | public | pkeys | 1 | | ROW |BEFORE | |

check_pkeys_fkey_cascade | UPDATE | public | pkeys | 1 | | ROW |BEFORE | |

check_fkeys2_fkey_restrict | DELETE | public | fkeys2 | 1 | | ROW |AFTER | |

check_fkeys2_fkey_restrict | UPDATE | public | fkeys2 | 1 | | ROW |AFTER | |

check_fkeys2_pkey_exist | INSERT | public | fkeys2 | 1 | | ROW |AFTER | |

check_fkeys2_pkey_exist | UPDATE | public | fkeys2 | 2 | | ROW |AFTER | |

check_fkeys_pkey2_exist | INSERT | public | fkeys | 1 | | ROW |AFTER | |

check_fkeys_pkey2_exist | UPDATE | public | fkeys | 1 | | ROW |AFTER | |

check_fkeys_pkey_exist | INSERT | public | fkeys | 2 | | ROW |AFTER | |

check_fkeys_pkey_exist | UPDATE | public | fkeys | 2 | | ROW |AFTER | |

check_pkeys_fkey_cascade | DELETE | public | pkeys | 1 | | ROW |AFTER | |

check_pkeys_fkey_cascade | UPDATE | public | pkeys | 1 | | ROW |AFTER | |

(10 rows)

DROP TABLE pkeys;

before insertorupdateon fkeys

after insertorupdateon fkeys

for each row

execute function

check_primary_key ('fkey1','fkey2','pkeys','pkey1','pkey2');

before insertorupdateon fkeys

after insertorupdateon fkeys

for each row

execute function check_primary_key ('fkey3','fkeys2','pkey23');

before insertorupdateon fkeys2

after insertorupdateon fkeys2

for each row

execute procedure

check_primary_key ('fkey21','fkey22','pkeys','pkey1','pkey2');

beforedeleteorupdateon pkeys

afterdeleteorupdateon pkeys

for each row

execute procedure

check_foreign_key (2,'cascade','pkey1','pkey2',

beforedeleteorupdateon fkeys2

afterdeleteorupdateon fkeys2

for each row

execute procedure check_foreign_key (1,'restrict','pkey23','fkeys','fkey3');