I wrote:
> Damned,
>
> while hacking down a little test suite for FOREIGN KEY
> (just to have some script based checking while doing
> the file buffering of the event queue), I discovered
> something looking wrong.
After rereading the part of the SQL3 spec in question, I saw that the checks I did for "triggered data change
violation"where wrong.
The just committed changes to the trigger manager and related areas cause ANY second change of a value, possibly
referencedby a foreign key, to bomb out with the above exception. So the example below doesn't work any more.
That means, that a row cannot get deleted, if it has been inserted or possibly referenced attributes updated
insidethe same transaction. Also, possibly referenced attributes cannot be changed twice inside one and the same
transaction.The previous "event condensing" is gone.
The benefit is, that since the trigger manager now checks for RI_FKey... triggers, if the referenced attributes
changewhile adding the event to the queue, he will suppress the real trigger call at all if the key's are equal.
Thissaves fetching back OLD and NEW at the time, the checks have to be executed.
> Having the following table schema:
>
> CREATE TABLE t1 (
> a int4 PRIMARY KEY,
> b int4
> );
>
> CREATE TABLE t2 (
> c int4,
> d int4,
>
> CONSTRAINT t2_d_t1_a FOREIGN KEY (d)
> REFERENCES t1 MATCH FULL
> ON UPDATE CASCADE
> DEFERRABLE INITIALLY IMMEDIATE
> );
>
> I can do the following:
>
> BEGIN;
> SET CONSTRAINTS ALL DEFERRED;
> UPDATE t1 SET a = 99 WHERE a = 1;
> UPDATE t1 SET a = 1 WHERE a = 2;
> UPDATE t1 SET a = 2 WHERE a = 99;
> COMMIT;
>
> to swap t1.a 1<->2.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #