Stop invalid data in a attribute with foreign key constraint using triggers?

Question

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 ?

Answer 1

Wouldn't a foreign key constraint better achieve what you want?

ALTER TABLE [dbo].[TABLE2]  WITH CHECK 
ADD  CONSTRAINT [FK_TABLE2_TABLE1] FOREIGN KEY([FK_COLUMN])
REFERENCES [dbo].[TABLE1] ([PK_COLUMN])
GO

This is what foreign key constraints are meant to do - specifically, not allow a record to be inserted that violate the foreign key relationship.

Note that to make this example more readable, I used different column and table names - S, A, R and B looked like a mess.