Update table after delete using trigger

Question

As the title states, I'm trying to use a trigger to update a value in Table2 when a row gets deleted from Table1. My code looks like this:

CREATE TRIGGER TRG_Table1_DEL
    ON Table1
    FOR DELETE
AS
    UPDATE Table2 SET
        FK_Table1ID = NULL
    WHERE FK_Table1ID = (SELECT Table1ID FROM DELETED)

The first error I get is with the UPDATE command and that states: "Incorrect syntax near 'UPDATE'. Expecting EXTERNAL.
The second error is with the DELETED table that I'm trying to access. This states "Invalid object name 'DELETED'.

Overall, I'm just trying to mimic a constraint that sets the column to null (ON DELETE SET NULL). I'm doing this because I got an error saying I might cause multiple cycles or cascade paths when trying to add the constraint.

For my code, I based it off this answer.

Please note you need to handle the fact that `Deleted` might have multiple rows i.e. this `WHERE FK_Table1ID = (SELECT Table1ID FROM DELETED)` should be this `WHERE FK_Table1ID IN (SELECT Table1ID FROM DELETED)` — Dale K, Mar 16 '21 at 19:18
You should probably also add both `SET NOCOUNT ON;` and `IF(NOT EXISTS(SELECT 1 FROM deleted)) RETURN;` to the trigger. Little tip: if you have a FK cycle, there is a good chance that something is wrong with the design of your DB. Not always true at all, just something to think about. — Charlieface, Mar 16 '21 at 19:33
I don't see how you can get that first error with the code you showed us. Are you sure this is the entire code ? — GuidoG, Mar 17 '21 at 06:48

score 0 · Answer 1 · edited Mar 16 '21 at 19:25

This should work in SQL Server. I have just replaced (=) with (in).

CREATE TRIGGER TRG_Table1_DEL
ON Table1
FOR DELETE
AS
    UPDATE Table2 
    SET FK_Table1ID = NULL 
    WHERE FK_Table1ID IN (SELECT Table1ID FROM DELETED)

Example:

 create table empsalary(empid int, month int , salary int);

 insert into empsalary values(1, 1, 100);
 insert into empsalary values(1, 2, 101);
 insert into empsalary values(1, 3, 103);

 create table emp(empid int, id int);

 insert into emp values(1,1);


CREATE TRIGGER TRG_Table1_DEL
ON empsalary
FOR DELETE
AS
    UPDATE emp 
    SET id = NULL 
    WHERE empid IN (SELECT empid FROM DELETED)
     

 select  * from empsalary

empid	month	salary
1	1	100
1	2	101
1	3	103

 select  * from emp

empid	id
1	1

 delete empsalary where month=1;

 select * from empsalary;

empid	month	salary
1	2	101
1	3	103

 select  *from emp;

empid	id
1	null

db<>fiddle here

m0kova01 · Accepted Answer · 2021-03-16T19:30:40.290

0

The problem turned out to have a very simple fix. I was getting the errors because I was trying to add the trigger after dropping a constraint. This is fine, but I forgot to add a GO command in between the dropping of the constraint and the creation of the trigger. My code looked like this:

DROP CONSTRAINT
ADD TRIGGER

but this was the correct solution:

DROP CONSTRAINT
GO
ADD TRIGGER

Edit: as DaleK has pointed out, I also need to modify the WHERE clause in case the DELETED table has multiple rows

edited Mar 16 '21 at 19:30

answered Mar 16 '21 at 19:25

m0kova01

149
1
12

1

Still, make sure you sort the `=` versus `IN` problem. – Dale K Mar 16 '21 at 19:25
1

But you need to modify the condition in where clause if there is any chance to delete more than one row at a time. – Kazi Mohammad Ali Nur Romel Mar 16 '21 at 19:27

Update table after delete using trigger

2 Answers2