Sql – Before trigger in SQL Server

sqlsql-servertriggerstsql

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.

Best Solution

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(SQL.80).aspx