Clever Geek Handbook

SQL Action Keyword

What does the SQL Action keyword do? Can I use this keyword in a trigger and determine if the trigger was invoked by an insert, delete, or update?

0


source to share


2 answers


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

To create a trigger at specific steps, all you have to do is specify which ones are executed when the trigger is created. You don't have to put all three like the example below, you can place any combination of the three depending on your goal.

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


If you need to determine which one is called a trigger, you should check the inserted and deleted tables as shown 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
  ...
+4


source


No, you cannot use it for this. But you can look at Inserted and Deleted to see what happened.

Basically, a basic example will look 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, however, it just depends on what you have to do. In some cases, you can just join these tables using the primary key and do different things.

If you post more details in your question about what you are trying, it would be easier to provide a more specific answer.

+1


source






More articles:


All Articles