Creating a Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Creating a Trigger

Author

Topic

DEBBIECOATES
Starting Member

1 Post

Posted - 2007-10-22 : 13:38:51

I have 3 tables on my db, Projects, ProljectAllocationLog and Users

Project consists of Projectid(PK), ProjectName, UserID
ProjectAllocationLog consists of ProjectAllocationID(PK), Projectid, UserID,Date
Users consists of UserID (PK), Fullname

Over the course of time the user allocated to a project may change. The db has been set up to always show the most current user in the UserID of the Projects table,

I want to create a log that will record everytime that user has changed. (ProjectAllocationLog)

Having read through some examples posted on Forums, I believe that I can do this with a trigger, but I am not sure if I am doing it right, the trigger I have written is

Create Trigger tr_UpdateAllocationLog
ON Projects
AFTER Update
AS
If NOT UPDATE (Userid)

DECLARE @PROJECTID
DECLARE @NEWUSER
DECLARE @PREVIOUSUSER

SET @PROJECTID= (SELECT projected FROM Inserted)
SET @NEWUSER = (SELECT UserID from Inserted)
SET @ PREVIOUSUSER = (SELECT UserID from Deleted)

If @NEWUSER <> @PREVIOUSUSER

INSERT INTO ProjectAllocationLog (ProjectID, UserID, Date) VALUES (@PROJECTID, @NEWUSER, GETDATE())

Go

I would appreciate any comments

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 02:27:39

You need to write your trigger assuming that more than one row will have been changed. its a common misconception that the trigger is called for each ROW changed - actually its called one for each statement, so that may result in multiple rows being changed


INSERT INTO ProjectAllocationLog (ProjectID, UserID, Date) 
SELECT I.projected, I.UserID, D.UserID, GetDate()
FROM inserted AS I
     JOIN deleted as D
         ON D.Projectid = I.Projectid

What about a trigger on Delete?

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Triggers,Trigger,audit%20changes

Kristen