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: 275
        
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