Need some help with Sql Server and a simple Trigger.

Question

Hi folks,

I wish to make a trigger but i'm not sure how to grab the data for whatever caused the trigger.

I have a simlpe table.

FooId INT PK NOT NULL IDENTITY
Name VARCHAR(100) NOT NULL

I wish to have a trigger so that when an UPDATE, INSERT or DELETE occurs, i then do the following.

Pseduocode

IF INSERT
    Print 'Insert' & Name
ELSE IF UPDATE
    Print 'Update' & FooId & Name
ELSE IF DELETE
    Print 'Delete' & FooId & Name

Now, I know how to make a trigger for a table. What i don't know how to do is figure out the values based on what the trigger type is.

Can anyone help?

Edit: Not sure if it helps, but db is Sql Server 2008

Answer 1

SQL triggers provide an implicitly-defined table called "inserted" which returns the affected rows, allowing you to do things like

UPDATE mytable SET mytimestamp = GETDATE() WHERE id IN (SELECT id FROM inserted)

Regarding your code sample, you'll want to create separate INSERT, UPDATE and DELETE triggers if you are performing separate actions for each.

(At least, this is the case in SQL Server... you didn't specify a platform.)

Answer 2

the pseudo table "inserted" contains the new data, and "deleted" table contains the old data.

You can do something like

create trigger mytrigger on mytable for insert, update, delete
as
    if ( select count(*) from inserted ) > 0
        -- insert or update
        select FooId, Name from inserted
    else
        -- delete
        select FooId, Name from deleted

To clarify all the comments made by others, on an insert, the inserted table contains data and deleted is empty. On a delete, the situation is reversed. On an update, deleted and inserted contain the "before" and "after" copy of any updated rows.

Answer 3

On 2008, there is also MERGE command. How do you want to handle it?

Starting from 2008, there are four commands you can modify a table with: INSERT, UPDATE, DELETE, and MERGE:

http://blogs.conchango.com/davidportas/archive/2007/11/14/SQL-Server-2008-MERGE.aspx

http://sqlblogcasts.com/blogs/grumpyolddba/archive/2009/03/11/reasons-to-move-to-sql-2008-merge.aspx

What do you want your trigger to do when someone issues a MERGE command against your table?

Answer 4

When you are writing a trigger, you have to account for the fact that your trigger may be called by a statement that effects more than one row at a time.

As others have pointed out, you reference the inserted table to get the values of new values of updated or inserted rows, and you reference the deleted table to get the value of deleted rows.