You Asked
Tom, can you help me to handling error 2291 on a trigger, please check this case test:
DROP TABLE T1 CASCADE CONSTRAINT;
CREATE TABLE T1
( a INT NOT NULL,
b INT NOT NULL,
CONSTRAINT pk_t1 PRIMARY KEY (a,b) );
DROP TABLE T2;
CREATE TABLE T2
( x INT NOT NULL,
y INT NOT NULL,
a INT NOT NULL,
b INT NOT NULL,
CONSTRAINT pk_t2 PRIMARY KEY (x,y,a),
CONSTRAINT fk01_t1 FOREIGN KEY (a,b) REFERENCES T1(a,b) );
--TRG_AI_T1
CREATE OR REPLACE TRIGGER TRG_AI_T1
AFTER INSERT ON T1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
UPDATE T2 SET b = :NEW.b WHERE a = :NEW.a;
END;
/
--TRG_BD_T1
CREATE OR REPLACE TRIGGER TRG_BD_T1
BEFORE DELETE ON T1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
no_parent EXCEPTION;
PRAGMA EXCEPTION_INIT(no_parent, -2291);
BEGIN
UPDATE T2 SET b = :OLD.b-1 WHERE a = :OLD.a;
EXCEPTION
WHEN no_parent THEN
DELETE T2 WHERE a = :OLD.a;
END;
/
INSERT INTO T1 VALUES(1,1);
INSERT INTO T1 VALUES(2,1);
INSERT INTO T2 VALUES(1,1,1,1);
INSERT INTO T2 VALUES(1,2,2,1);
INSERT INTO T1 VALUES(1,2);
INSERT INTO T1 VALUES(2,2);
COMMIT;
DELETE T1 WHERE a=2 AND b=2;
DELETE T1 WHERE a=2 AND b=1;
ORA-02291: integrity constraint (BNFINVERSION.FK01_T1) violated - parent key not found
but if I do the delete on t2 by pl/sql block, it works,
DECLARE
no_parent EXCEPTION;
PRAGMA EXCEPTION_INIT(no_parent, -2291);
BEGIN
UPDATE T2 SET b = 0 WHERE a = 2;
EXCEPTION
WHEN no_parent THEN
DELETE T2 WHERE a = 2;
END;
PL/SQL procedure successfully completed
I need to do that by the trigger "TRG_BD_T1"
and Tom said...
Word of caution: getting "fancy" with triggers -- trying to have 15 side effect automagically happen by accident -- is a really bad idea (tm). A truly bad idea.
2 years from now, it'll confuse you (won't even take 2 years actually)
One minute from now, it'll confound others having to work on your system (hey look, Oracle is broke -- I do this and see what happens)
Maintaining this over time -- really hard, enhancing -- really hard, debugging ALL CASES -- almost impossible.
Triggers should be confined to "the simple". Complex default values, validation -- using them like this would be a bad idea.
constraints are relaxed during the execution of a statement and verified at the end of the statement (else most modifications that affect more than one row could never execute, it is not checked row by row, but rather statement by statement and triggered code is part and parcel of that statement)
Now, it looks like "A" is the true foreign key here, "B" is the "version i point to". "B" should be removed from the child entirely perhaps with the rule that "as a child, I point to the most current existing version" which seems to be what you are trying to do.
If that is not what you are trying to do, explain your goal instead of your implementation and we'll take a look.
But take heed on the advice to avoid triggers to do "really fancy stuff". What do you do when someone issues a MERGE statement in 10g that simultaneously trips off INSERT, UPDATE and DELETE triggers (or any combinantion thereof)! (whoops is probably what you would say, didn't think about that). Or in 9i when merge always fires the update and insert triggers. Or <case 523413 that you didn't think about here>.
If you truly need this logic, it is screaming for a "linearly executed stored procedure that is easily understood by everyone"
Rating
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment