[egof]

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.



This is the data in my two tables



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.


It should look like this.


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.

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.

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.

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.

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.

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.



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

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.

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.

Attached File(s)

•  Scripts.txt (2.11K)
  Number of downloads: 414
•  MS2005TriggerTutorial.doc (725.5K)
  Number of downloads: 462

[PsychoCoder]

Fixed your code tags