SQL Action keyword

Question

What does the SQL Action keyword do? Can I use this keyword in a trigger and determine if the trigger was called by an Insert, Delete or Update?

Answer 1

No, you can't use it for that. But you can look at the Inserted and Deleted tables to see what happened.

A vary basic example would go something like this:

If exists (select * from inserted) and exists (select * from deleted)
    --Update happened
If exists (select * from inserted)
    --Insert happened
If exists (select * from deleted)
    --Delete happened
Else
    --Nothing happened

Realistically, though, it just depends on what you need to do. In some cases, you might just end up joining on these tables by primary key and do various things.

If you post more details in your question about what you're attempting, though, it'd be easier to give a more specific response.

Answer 2

ACTION is reserved for use in ODBC function calls. These words do not constrain the minimum SQL grammar; however, to ensure compatibility with drivers that support the core SQL grammar, applications should avoid using these keywords.

To create a trigger on certain steps, all you have to do is specify which ones during the creation of the trigger. You don't have to put all three like the example below you can place any combination of the 3 choices depending on your target

CREATE TRIGGER TriggerName
ON TableName
  [FOR|AFTER|INSTEAD OF]
  AFTER,UPDATE,DELETE
AS
 ...

If you need to determine which one of these called the trigger you should check the inserted and deleted tables like below.

IF EXISTS (SELECT TOP 1 * FROM Inserted) AND NOT EXISTS (SELECT TOP 1 * FROM Deleted) --INSERT
  ...

IF EXISTS (SELECT TOP 1 * FROM Inserted) AND EXISTS (SELECT TOP 1 * FROM Deleted) --UPDATE
  ...

IF NOT EXISTS (SELECT TOP 1 * FROM Inserted) AND EXISTS (SELECT TOP 1 * FROM Deleted) --DELETE
  ...