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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    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
as
declare @id_order int,@id_line int;

If exists (Select * from inserted) and not exists(Select * from deleted)
begin    
   
    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);
end

If exists(select * from deleted) and not exists(Select * from inserted)
begin 
    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;
end

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