Database Tutorials

Welcome to Dream.In.Code
	Become an Expert! Join 306,975 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,845 people online right now. Registration is fast and FREE... Join Now! Chat LIVE With a Expert

MS 2005 Trigger Tutorial

MS 2005 Trigger Tutorial, This shows how to add a trigger to a MS 2005 database

Options

egof

6 Apr, 2009 - 04:51 AM

Post #1

Assume we have a book store app. A person logs into the app and makes changes to a book record. The app makes the changes to the record and marks the ModifiedByID column with the current users ID. Now we need to know the person’s name for convenience. We could modify the app to do this or we can add a trigger to pull in that information.

We will have 2 tables in this database, tblBook and tblUser.

IPB Image

This is the data in my two tables
IPB Image

When a record is modified in the book table we will do a look up there so the trigger will need to go on that table. Right click on the triggers folder and add a new trigger.
IPB Image

It should look like this.
IPB Image

When you right clicked on that table you probably thought it would fill in a lot of the blanks for you but you were wrong, it gives you a complete blank template to start from.

You will need to tell it which table to attach the trigger to and when to fire it, after an insert, delete, update or all of them.
We want it attached to the Book table and only after a record has been updated.
Here is our start.

CODE

CREATE TRIGGER dbo.trg_tblBook_Update
   ON  dbo.tblBook
   AFTER UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here

END
GO

Its name is trg_tblBook_Update but don’t forget to add the dbo. The ON part says which table to add the trigger to and the AFTER section tells it when the trigger should fire, in this case after the row has been updated.

Im going to be doing small incremental changes so I want to remove the trigger each time. This is the statement I’ll use to remove the trigger.

CODE

if exists(select * from sys.objects where name='trg_tblBook_Update')
begin
print 'del trg_tblBook_Update'
drop trigger trg_tblBook_Update
end
go

Triggers aren’t easy to build since they aren’t using the correct table. Instead they use these two tables Inserted and Deleted. What do these two tables look like? Well if your in the trigger that is working on the tblBook table then they look exactly like tblBook. They will have the same columns. The Inserted table will contain the updated row. The Deleted table contains the old data before it gets dumped. If you wanted to keep a history of changes you’d grab the data from the Deleted table.

I start outside of the trigger and make a normal SQL statement to do what I want. I pick a row from the Book table to use.
Im going to join the book and the user tables to get the user name that Im going to add to the update the book table with.

CODE

select *
from tblBook book join tblUser usr on usr.UserID=book.ModifiedByID
where book.BookID=1

Now I narrow it down to the two columns that Im interested in. ModifiedByName is what I want to update and UserName is what I want to update it with.

CODE

select book.ModifiedByName,usr.UserName
from tblBook book join tblUser usr on usr.UserID=book.ModifiedByID
where book.BookID=2

Now I adjust the SQL to do the update.

CODE

Update tblBook set ModifiedByName=usr.UserName
from tblBook book join tblUser usr on usr.UserID=book.ModifiedByID
where book.BookID=2

This is what it did.

IPB Image

Now I need to modify it to work in a trigger. Add the Inserted table into the script.
This is my final trigger script.

CODE

if exists(select * from sys.objects where name='trg_tblBook_Update')
begin
    print 'del trg_tblBook_Update'
    drop trigger trg_tblBook_Update
end
go

CREATE TRIGGER dbo.trg_tblBook_Update
   ON  dbo.tblBook
   AFTER UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    update tblBook set ModifiedByName=usr.UserName
    from tblBook book join tblUser usr on usr.userid=book.ModifiedByID
        join Inserted i on i.bookid=book.bookid

END
GO
print 'trg_tblBook_Update'

If your part of a large corporate environment you will want to find out about their policies on triggers and “with (nolock)” hints.

Now lets go to the Book table and make some changes.
Im going to run these scripts.

CODE

select *
from tblBook

update tblBook set BookTitle='Triggers 101',ModifiedByID=1
where bookID=1

select *
from tblBook

This first one will show you what you currently have then I make some changes and the I show the results.

IPB Image