Before trigger in SQL Server

Question

I have 2 tables: survey (id(PK), name) and survey_to_topic (survey_id(PK,FK,not null), topic_id(PK,FK,not null)). When I try to delete from survey table, I get exception:

"The DELETE statement conflicted with the REFERENCE constraint "FK_survey _to _topic _survey". The conflict occurred in database "mydatabase", table "dbo.survey _to _topic", column 'survey _id'."

So to get no error first I must delete record from table survey_to_topic and after that from table survey. I think it is better to do with before trigger on table survey, but I can't find any information about this. There are a lot of articles about before triggers in PL/SQL, but I use SQL Server.

Answer 1

You can add ON DELETE CASCADE to the relationship between the two tables, and the records from the survey_to_topic table will be deleted automatically.

See http://msdn.microsoft.com/en-us/library/aa933119%28SQL.80%29.aspx

Answer 2

You can use ON DELETE CASCADE. This is added to the table containing the FK.

See example here.

Answer 3

As Alex Deem and astander already mentioned - you should use ON DELETE CASCADE on your foreign key relationship - that handles this scenario automatically for you.

SQL Server doesn't know the concept of BEFORE (operation) TRIGGERs - SQL Server has AFTER triggers, or then INSTEAD OF triggers. See the Introduction to triggers article for some background info.

But ON DELETE CASCADE is definitely the easiest way to do this.

Answer 4

As everyone else here mentioned, ON DELETE CASCADE is a way to go -- as long as you are aware of consequences; there is a reason why ON DELETE NO ACTION (raise error) is the default. In other words, you should plan your deletion strategy -- it is too easy to wipe out rows from several tables unintentionally by using ON DELETE CASCADE.