RI generic trigger procs

So co-developers,

let's go.

I think the best is if all RI triggers share the same call
interface. My favorite for now is this:

RI_FKey_<operation>_<event> ( <args> )

Where <operation> is one of check, cascade, restrict, setnull
or setdefault and where <event> is one of ins, upd or del.
Thus, the trigger proc to check foreign key on insert would
be RI_FKey_check_ins() and the one to do cascaded deletes
becomes RI_FKey_cascade_del().

The <args> is allways '<constraint_name>', '<FK_table_name>',
'<PK_table_name>' and a variable length list of
'<FK_attribute_name>', '<PK_attribute_name>' pairs. In the
case of an unnamed constraint, the <constraint_name> is given
as '(unnamed)'. It is up to CREATE TABLE parsing/utility to
build these arguments properly.

All the procs should use prepared and saved SPI plans
wherever possible.

Any combination of attributes in a table referenced to by one
or more FOREIGN KEY ... REFERENCES constraint of another
table shall have a UNIQUE and NOT NULL constraint. I don't
think that it's easy to assure this and to avoid that the
underlying unique index isn't dropped later. First it shall
be enough to document and leave it up to the
creator/maintainer of the database schema. So we assume here
that any PK is unique and cannot contain NULL's.

The FOREIGN KEY itself might allow NULL's, but since we
implement only MATCH FULL right now, either all or none of
the FK attributes may contain NULL. We'll check this in
RI_FKey_check_ins() and RI_FKey_check_upd().

The behaviour of the procs in detail (this is what ya shall
write - they don't exist):

RI_FKey_check_ins()

Implements "FOREIGN KEY ... REFERENCES ..." at insert
time.

Fired AFTER INSERT on FK table.

First off, either all or none of the FK attributes in NEW
must be NULL. If all are NULL, nothing is checked and
operation goes through. Otherwise it raises an ERROR if
the given key isn't present in the PK table.

RI_FKey_check_upd()

Implements "FOREIGN KEY ... REFERENCES ..." at update
time.

Fired AFTER UPDATE on FK table.

If all FK attributes in OLD and NEW are the same, nothing
is done. Otherwise, the operation is the same as for
RI_FKey_check_ins().

RI_FKey_cascade_del()

Implements "FOREIGN KEY ... ON DELETE CASCADE".

Fired AFTER DELETE on PK table.

It deletes all occurences of the deleted key in the FK
table.

RI_FKey_cascade_upd()

Implements "FOREIGN KEY ... ON UPDATE CASCADE".

Fired AFTER UPDATE on PK table.

Nothing happens if OLD and NEW keys in PK are identical.
Otherwise it updates all occurences of the OLD key to the
NEW key in the FK table.

RI_FKey_restrict_del()

Implements "FOREIGN KEY ... ON DELETE RESTRICT".

Fired AFTER DELETE on PK table.

Checks if the deleted key is still referenced from the FK
table and raises an ERROR if so.

RI_FKey_restrict_upd()

Fired AFTER UPDATE on PK table.

Nothing happens if OLD and NEW keys in PK are identical.
Otherwise checks if the OLD key is still referenced from
the FK table and raises an ERROR if so.

RI_FKey_setnull_del()

Implements "FOREIGN KEY ... ON DELETE SET NULL"

Fired AFTER DELETE on PK table.

Updates all occurences of the OLD key to NULL values in
the FK table.

RI_FKey_setnull_upd()

Implements "FOREIGN KEY ... ON UPDATE SET NULL"

Fired AFTER UPDATE on PK table.

Nothing happens if OLD and NEW keys in PK are identical.
Otherwise updates all occurences of the OLD key to NULL
values in the FK table.

RI_FKey_setdefault_del()

Implements "FOREIGN KEY ... ON DELETE SET DEFAULT"

Fired AFTER DELETE on PK table.

Updates all occurences of the OLD key in FK table to the
default values defined in the schema of the FK table.

RI_FKey_setdefault_upd()

Implements "FOREIGN KEY ... ON UPDATE SET DEFAULT"

Fired AFTER UPDATE on PK table.

Nothing happens if OLD and NEW keys in PK are identical.
Otherwise updates all occurences of the OLD key in FK
table to the default values defined in the schema of the
FK table.

This all is the behaviour of FOREIGN KEY ... MATCH FULL
according to the SQL3 standard - as I understood it. I know
that the above trigger procs aren't easy to implement. But
after all, many of the referential action ones look very
similar to each other.

One general thing required is IMHO some hashtable(s) living
in the cache context where any trigger once fired can cache
information needed again and again (like the saved plans,
functions for equality checks on OLD vs. NEW, etc.).

The bazar is open - come in.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck(at)debis(dot)com (Jan Wieck) #