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

You need to take into account that the trigger is fired **once per statement** (and **NOT** once per row as many devs believe) and that the `Deleted` pseudo table could contain **multiple rows** (if your statement deleted multiple rows) — marc_s, Apr 03 '12 at 15:41
@marc_s - In the system, only one row could possibly be deleted at a time (application front-end). Could you elaborate on what you mean. Is it as simple as changing `WHERE id = deleted.id` to `WHERE id IN (SELECT id FROM deleted)`? — Shawn H., Apr 03 '12 at 15:52
@ShawnH. Yes it should be that simple. I think Marc means if somehow a mass delete was called from somewhere the trigger would only fire once for the whole statement rather than per row, so using an `IN` should sort it either way. — Bridge, Apr 03 '12 at 15:59
@ShawnH: yes, basically that change would work - just don't expect/assume that the `Deleted` table always has just a single row - it might have multiple. So your original statement `....WHERE id = deleted.id` would fail miserably in other systems if there are multiple rows in the `Deleted` pseudo table — marc_s, Apr 03 '12 at 16:00
Cross database delete trigger. Makes me think these belong in one database and should have proper referential integrity. Any time you're trying to enforce referential integrity across databases or enforce referential integrity in triggers I suspect a design problem. Since you have committed to having the front end enforce referential integrity by having these multiple databases, and you have committed to having the front end only delete one row at a time, why not just move this into the sproc your front end uses to do the delete? — Brian White, Apr 03 '12 at 16:28
@BrianWhite - This would make sense but the application and associated database are ancient and unsupported third-party software. Changes to the schema breaks all functionality, hence the second database used for all new add-on functionality that I'm working on. It causes major headaches at times. — Shawn H., Apr 03 '12 at 17:24

score 68 · Accepted Answer · answered Apr 03 '12 at 15:54

68

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

answered Apr 03 '12 at 15:54

Magnus

45,362
8
80
118

Do the records from `table1` get deleted as well? Sorry if it sounds like a stupid question... – Matthieu Oct 15 '19 at 16:36
1

@Matthieu yes rows in table 1 will also be deleted.Those are the rows that are accessed in table `deleted` in the example above. – Magnus Oct 16 '19 at 10:24
Thanks @Magnus. I wasn't sure because if you use `INSTEAD OF DELETE` instead of `FOR DELETE` (thank you formatting ;)), then the records don't get deleted unless explicitly specified in the trigger. – Matthieu Oct 16 '19 at 10:52
1

Shouldn't the trigger be "ON database2.dbo.table2"? I mean, the OP wanted a "...trigger that conditionally deletes rows from database1.table1 **when a row from database2.table2 is deleted**". – Mikko Östlund Mar 12 '21 at 20:08

score 10 · Answer 2 · edited Nov 28 '13 at 01:44

10

Better to use:

DELETE tbl FROM tbl INNER JOIN deleted ON tbl.key=deleted.key

edited Nov 28 '13 at 01:44

Qantas 94 Heavy

15,750
31
68
83

answered Nov 28 '13 at 01:26

Scott Rayner

101
1
2

You mean use that in the `AS ...` trigger body? – Matthieu Oct 15 '19 at 16:36

score 3 · Answer 3 · edited May 23 '17 at 12:34

3

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

edited May 23 '17 at 12:34

Community

1
1

answered Feb 14 '14 at 11:07

Narkha

1,197
2
12
30

According to most of the answers in the linked SO question, it doesn't make a difference if you use `IN` or `EXISTS`. The DB engine will produce the exact same execution plan. – eddex Jul 21 '21 at 08:13

score 3 · Answer 4 · answered Apr 03 '12 at 15:53

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)

SQL Server ON DELETE Trigger

4 Answers4

Linked