CODEWITHSUNDEEP
sqlsql-server
Hady.J.Wolf
Hady.J.Wolf

Reputation: 5

Delete after Insert trigger

I'm trying to create after insert trigger to delete a row from table whenever I insert the same row in the opposite table using the ID

But it doesn't work

Create Trigger [dbo].[MeritedDeceased] On [dbo].[Deceased]
After Insert
As
Begin
Delete From dbo.Merited Where dbo.Deceased.ID = dbo.Merited.ID
End

Upvotes: 0

Views: 9572

Answers (1)

3BK
3BK

Reputation: 1348

I think you mean to do something like this (maybe)...

CREATE TRIGGER [dbo].[MeritedDeceased] ON [dbo].[Deceased]
AFTER INSERT
AS
BEGIN
    DELETE M
    FROM [dbo].[MeritedDeceased] M
    INNER JOIN Inserted I
        ON I.ID = M.ID
    ;
END
;

Translation: Every time a row is inserted into dbo.Deceased, delete any rows with the same (inserted) ID from dbo.MeritedDeceased.

FYI, this TRIGGER won't just delete one row, but also a batch of rows that were inserted together. If that's what you want, then this should help.

Upvotes: 4

Related Questions