CODEWITHSUNDEEP
sql-servertriggers
Arcadian
Arcadian

Reputation: 4350

What is the best way to figure out if an UPDATE, DELETE or INSERT occur for a trigger

I want to create a single trigger in MS SQL for INSERT, UPDATE and DELETE but I want to be able to simple extract the data without 3 IF statements

I want to do something like this:

DECLARE @PK  int

SELECT @PK = COALESCE(i.PK, d.PK)
FROM inserted i, deleted d

This did not work but would like to know if I can do it in one query.

What are some alternatives?

Upvotes: 0

Views: 78

Answers (2)

Arcadian
Arcadian

Reputation: 4350

I ended up using a FULL OUTER JOIN

DECLARE @DataIWanted as varchar(255)

SELECT @DataIWanted = COALESCE(i.TheData,d.TheData)
FROM inserted i
   FULL OUTER JOIN deleted d on d.PK=i.PK

The query above will be from the deleted table or the inserted/updated table. With the assumption that TheData is defined as NON NULL in the DB.

Upvotes: 0

RandomUs1r
RandomUs1r

Reputation: 4190

You can do the switch logic found here: SQL Server Trigger switching Insert,Delete,Update

Or you can create 3 different triggers.

Those are you options.

Upvotes: 1

Related Questions