CODEWITHSUNDEEP
sql-server
Simran
Simran

Reputation: 569

Creating update trigger in SQL Server

I need to delete rows from Table A with row ids same as those rowId's which were updated in Table B. so, I am assuming I need to create an after update trigger.

For example: if time column in rows with row ID 1, 2, 3 was updated of Table B, then Table A should delete rows with row ID 1, 2 and 3.

This is what I have tried:

CREATE TRIGGER trgAfterUpdate ON [dbo].[Patients] 
FOR UPDATE
AS  
    DECLARE @row INT

    SELECT @row = (SELECT RowId FROM Inserted)

    IF UPDATE (ModifiedAt)
       DELETE FROM dbo.CACHE 
       WHERE Cache.RowId = @row
GO

However, if there was a batch update, trigger would be fired only once. How do I delete all rows from dbo.Cache with rowid same as those updated in dbo.Patients ?

Upvotes: 1

Views: 541

Answers (3)

Rhys Jones
Rhys Jones

Reputation: 5508

You have to write the trigger in a set-based way, such as;

CREATE TRIGGER trgAfterUpdate ON [dbo].[Patients] FOR UPDATE
AS  

   if update(ModifiedAt)
      delete c from dbo.CACHE c INNER JOIN inserted i on i.RowId = c.RowId

GO

Upvotes: 2

Jeroen Mostert
Jeroen Mostert

Reputation: 28769

inserted is a table that contains all the updates; you can just use it as such.

CREATE TRIGGER trgAfterUpdate ON [dbo].[Patients]
FOR UPDATE
AS BEGIN
    SET NOCOUNT ON;
    IF TRIGGER_NESTLEVEL() > 1 RETURN;

    IF UPDATE([ModifiedAt])
        DELETE dbo.Cache WHERE RowID IN (SELECT RowID FROM inserted)
END

Note that I'm using SET NOCOUNT ON to ensure the trigger doesn't return any extra result and a TRIGGER_NESTLEVEL() check to prevent problems with recursion (not a problem for a single trigger, but a good idea in general).

Upvotes: 1

flo
flo

Reputation: 10241

You can use DELETE ... FROM:

DELETE FROM dbo.Cache FROM dbo.Cache JOIN inserted I ON I.RowId=Cache.id

Upvotes: 0

Related Questions