Trigger operation



  • 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♪ ♪ ♪



  • 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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2