How to create an Insert Delete trigger associated with another datatable RRS feed

  • Question

  • User1670624291 posted

    I have my Orders_Info data table and I intend to associate my table with another Order_Info_Status table. My idea is whenever a new line is inserted in my Order_Info table, a new line is created in the Order_Info_Status table with the primary keys.I think the best way to accomplish this is with the use of a trigger to launch the Insert or a Delete.

    Can someone help me build an Insert and Delete trigger for my example to use with reference?

    Thanks to anyone who can help me!

    Friday, January 22, 2021 4:33 PM


  • User-1412735316 posted

    If you insert any row in order_info table Order_trigger will insert a row in order_info_status table with the value id_order and id_line from order_info and status and number field will be null. 

    If you delete any row from order_info table a row will be deleted from order_info_status with the same id_order and id_line as order_info

    Here goes your desired triggfer:

    create trigger Order_trigger
    on Order_Info
    after INSERT, DELETE
    declare @id_order int,@id_line int;
    If exists (Select * from inserted) and not exists(Select * from deleted)
        SELECT @id_order = id_order,@id_line=id_line from inserted i;
        INSERT into order_info_status(id_order,id_line) values(@id_order,@id_line);
    If exists(select * from deleted) and not exists(Select * from inserted)
        SELECT @id_order = id_order,@id_line=id_line from deleted d;
        delete order_info_status where id_order=@id_order and id_line=@id_line;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 22, 2021 8:20 PM