Trigger Trigger

Question

Why if I write a trigger to delete table T1

Table t1

id id_employeeFrom id_employeeTo 1 1 2 1 1 3 1 2 4 1 3 5 1 4 10 1 10 11

Trigger on table T1

 CREATE TRIGGER [TR_MyTrigger] ON T1 AFTER DELETE AS BEGIN DECLARE @id BIGINT SELECT TOP 1 @id = id FROM DELETED DELETE FROM T1 WHERE T1.id = @id AND T1.id_employeeFrom IN (SELECT id_employeeTo FROM DELETED) END

request

 DELETE FROM T1 WHERE id = 1 ` `AND id_employeeTo = 2

the trigger is triggered only once, that is, the table remains:

 id id_employeeFrom id_employeeTo 1 1 3 1 3 5 1 4 10 1 10 11

I need to stay

 id id_employeeFrom id_employeeTo 1 1 3 1 3 5

Why is this happening? In general, I can't pull records across the hierarchy, starting with id_employeeTo ...

And why should it be otherwise, if the predicate is used in the delete statement: id_employeeTo = 2?
There is a trigger on the table, I thought that it would work as I had planned, but it turns out not.

Accepted Answer · 2012-01-23T13:09:37

Trigger on operations caused by a trigger does not fire. Write a recursive query in the trigger.

And you do not handle the situation when several records with different id are deleted, which is bad.

Something like this:

 CREATE TRIGGER [TR_MyTrigger] ON T1 AFTER DELETE AS BEGIN with t as (select t1.id, t1.id_employeeFrom, t1.id_employeeTo from T1 inner join DELETED d on T1.id_employeeFrom = d.id_employeeTo and T1.id = d.id union all select t1.id, t1.id_employeeFrom, t1.id_employeeTo from T1 inner join t on t1.id_employeeFrom = t.id_employeeTo and T1.ID = T.ID) delete from T1 from T1 inner join t on t.id = t1.id and t1.id_employeeFrom = t.id_employeeFrom delete from T1 from T1 inner join deleted on T1.ID = DELETED.ID and T1.id_employeeTo = deleted.id_EmployeeFrom END

I'm sorry and somebody can help write such a request that something does not work ....?
I would like to clarify, where (with t as) every time a record is found, it searches for a new one on the cycle, right?
with t as and further is a recursive query, it goes down the tree, while there is at least one record obeying the already found.
In MS SQL there is a restriction - no more than 100 iterations in recursion by default, if the tree is deeper, it will fall.
We can say that in a loop, yes, as long as there are children.

Trigger Trigger

1 answer 1

More articles: