CODEWITHSUNDEEP
sql-servertriggers
Aimad Majdou
Aimad Majdou

Reputation: 35

Alternative solution for using triggers

I have a Table that has his PK as a FK in two other tables. I want when I delete a record from this table to delete records from the other tables based on the deleted row. I know that there is a solution which is using triggers, and here is mine:

CREATE TRIGGER Trig_Supp
    ON T_SALARIE
    FOR DELETE
AS
    DELETE FROM T_EVOLUTION
    WHERE Matricule = SELECT Matricule FROM deleted

    DELETE FROM T_EVALUATION
    WHERE Matricule = SELECT Matricule FROM deleted

GO

but our teacher told us to search for another solution which can do the same as this trigger does, but I couldnt find anything on the internet.

so is there really another solution to do that ?

Upvotes: 0

Views: 921

Answers (2)

Kahn
Kahn

Reputation: 1660

So what you're looking for is anything that will automatically handle the cascading deletes regardless of what kind of query was used to delete it.

As others already commented, the correct option would be to use delete cascade foreign keys, since clearly there's an implied foreign key connection there anyway. Also like Damien_The_Unbeliever said, without a such a constraint like that you could never remove the parent row without first removing the referencing child rows.

Upvotes: 2

John Bell
John Bell

Reputation: 2350

You could use the OUTPUT clause to capture deleted rows and then match and delete from your secondary tables. See BOL here.

The advantage of this is it will handle multiple rows.

Upvotes: 1

Related Questions