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

Filed under:
|
|
|

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

Related posts about sql

Related posts about mysql