Search code examples
sqlsql-servertriggersdelete-row

delete trigger to delete row in another table in another database sql

I have a database schema called Fulcrum that has a table called Orders with PK on OrderNO, OrderDate, TenantID

I have a bridge database schema called XFer where I also have a table called Orders with the same field names but no keys on OrderNO, OrderDate, TenantID

I need to create a trigger behind the Orders in Xfer that deletes the matching row in Fulcrum.dbo.Orders when I delete the row in XFer.dbo.Orders

thank you

Solution

  • Considering you are using Microsoft SQL Server syntax to create such trigger is :

    CREATE TRIGGER [XFer].[dbo].[OrderDeleted]
ON [Xfer].[dbo].[Orders]
AFTER DELETE
AS
BEGIN

   DELETE FROM [Fulcrum].[dbo].[Orders] WHERE [Fulcrum].[dbo].[Orders].[OrderNO] IN (SELECT [XFer].[dbo].[Orders].[OrderNO] FROM DELETED)  

END

    Obviously the syntax might not be perfect, but this is close to what you need.