Trigger operation

[shizuka]

Why if you write a trigger to remove 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

T1

CREATE TRIGGER [TR_MyTrigger] 
   ON  T1
   AFTER DELETE
AS 
BEGIN
  DECLARE  /users/3123/idanil  BIGINT
SELECT TOP 1  /users/3123/idanil  = id

FROM DELETED
DELETE FROM T1

WHERE     T1.id =  /users/3123/idanil 

AND T1.id_employeeFrom IN (SELECT id_employeeTo FROM DELETED)
END

Request

DELETE FROM T1

WHERE      id = 1

         AND id_employeeTo = 2

The trigger only works once, so 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?
Anyway, I can't get the hierarchy records, starting with id_employeeTo♪ ♪ ♪

[tahishae]

Trigger's not working on the trigger. Write back to the trigger for a study call.

And you don't process a situation where a few records of different ids are removed, which is bad.

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