SQL Server ON DELETE Trigger

Question

I m trying to create a basic database trigger that conditionally deletes rows from database1 table1 when a row from database2 table2 is deleted  I m new to triggers and was hoping to learn the best way to accomplish this  This is what I have so far  Suggestions   CREATE TRIGGER sampleTrigger     ON database1 dbo table1     FOR DELETE AS     IF EXISTS  SELECT foo                FROM database2 dbo table2                WHERE id   deleted id                AND bar   4   -- If there is a row that exists in database2 dbo table2  -- matching the id of the deleted row and bar 4  delete  -- it as well   -- DELETE STATEMENT   GO

User · Answer

I would suggest the use of exists instead of in because in some scenarios that implies null values the behavior is different  so  CREATE TRIGGER sampleTrigger     ON database1 dbo table1     FOR DELETE AS     DELETE FROM database2 dbo table2 childTable     WHERE bar   4 AND exists  SELECT id FROM deleted where deleted id   childTable id  GO

User · Answer

INSERTED and DELETED are virtual tables  They need to be used in a FROM clause   CREATE TRIGGER sampleTrigger     ON database1 dbo table1     FOR DELETE AS     IF EXISTS  SELECT foo                FROM database2 dbo table2                WHERE id IN  SELECT deleted id FROM deleted                 AND bar   4

User · Answer

Better to use   DELETE tbl FROM tbl INNER JOIN deleted ON tbl key deleted key

User · Answer

CREATE TRIGGER sampleTrigger     ON database1 dbo table1     FOR DELETE AS     DELETE FROM database2 dbo table2     WHERE bar   4 AND ID IN SELECT deleted id FROM deleted  GO

[sql-server] SQL Server ON DELETE Trigger

Examples related to sql-server

Examples related to sql-server-2005

Examples related to triggers