How to make a Delete Query inside a Trigger?

Question

I am doing an after insert trigger on a table where I delete the rows that are incorrect, however, I am unable to do this and I can't find the correct syntax anywhere.

From what I understand, the table where the rows will be deleted from is in the FROM clause but I also need to use the inserted table. How do I do this? I know this can be solved using an instead of insert trigger but I really want to know how to do this way.

DELETE 
FROM promotion p, inserted i 
WHERE <conditions>

Just join Inserted on your table (using an explicit join - not your old-school comma join) and delete from the alias of the table i.e. p. Check example D in the docs — Dale K
– Dale K, Commented Dec 8, 2022 at 20:54

Rahul Tripathi · Accepted Answer · 2022-12-08 20:54:37Z

1

You can try like this:

CREATE TRIGGER myTrigger ON dbo.MyTable
FOR INSERT
AS
SET NOCOUNT ON;
DELETE sometarget
FROM dbo.MyTable AS sometarget
JOIN inserted ON inserted.ID = sometarget.ID
WHERE <conditions>;
GO

answered Dec 8, 2022 at 20:54

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to make a Delete Query inside a Trigger?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related