Stop invalid data in a attribute with foreign key constraint using triggers?
Posted
by Eternal Learner
on Stack Overflow
See other posts from Stack Overflow
or by Eternal Learner
Published on 2010-05-13T23:01:31Z
Indexed on
2010/05/13
23:14 UTC
Read the original article
Hit count: 219
How to specify a trigger which checks if the data inserted into a tables foreign key attribute, actually exists in the references table. If it exist no action should be performed , else the trigger should delete the inserted tuple.
Eg: Consider have 2 tables R(A int Primary Key) and S(B int Primary Key , A int Foreign Key References R(A) ) .
I have written a trigger like this :
Create Trigger DelS
BEFORE INSERT ON S
FOR EACH ROW
BEGIN
Delete FROM S where New.A <> ( Select * from R;) );
End;
I am sure I am making a mistake while specifying the inner sub query within the Begin and end Blocks of the trigger. My question is how do I make such a trigger ?
© Stack Overflow or respective owner