Creating a Trigger

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 commentssomething like this is a little more graceful and handles multiple updates...

CREATE TRIGGER dbo.trg_ProjectStatusHistory
ON dbo.Project
FOR INSERT, UPDATE
AS
INSERT INTO ProjectStatusHistory(ProjectID,StatusID,SystemStat usID,DateChanged,UpdatedByUIDKey)
SELECT i.ProjectId,i.StatusId,i.SystemStatusId,GETDATE(), i.UpdatedByUIDKey
FROM inserted i
LEFT JOIN deleted d
ON i.ProjectId = d.ProjectID
WHERE i.StatusId <> d.StatusId OR i.SystemStatusId <> d.SystemStatusId
OR (i.StatusId is not null and d.Statusid is null)|||Briliant, thank you very much, I will do it the way you suggested, however, just out of curiosty, would my way have worked even though it was a very clumsey way of doing it?|||yours would not have worked for transactions that updated multiple rows and my memory is a little cloudy on this because I do not code a lot of triggers but I think I had a problem once calling records from one of the virtual tables and then calling records from another like you did here...

SET @.NEWUSER = (SELECT UserID from Inserted)
SET @. PREVIOUSUSER = (SELECT UserID from Deleted)

This might not work but I am not sure why. It was a while ago and if something happened more 3 days ago it might as well have never happened at all.|||That will only pull the first ID from inserted, and the first ID from deleted, and there is no guarantee that the two virtual tables will even be sorted identically.

You MUST use set-based operations in triggers.|||I dont understand what you mean by set based operations, can you explain please|||do not mind the blind dude. it's time for his medication.

the trigger in my first post is set based. he did not see it.|||but what does set based mean exactly? (Sorry to sound such a thicko)|||google "set based thinking"

here's one of many links:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/30/60192.aspx|||This is funny

I just had a request to build a "Project" app

I was so disdaned by the fact that I billed them a boatload and told them to buy MS Project and to send the users to class|||and I bet nobody blinked twice.|||do not mind the blind dude. it's time for his medication.

the trigger in my first post is set based. he did not see it.

I was referring to this:
SET @.NEWUSER = (SELECT UserID from Inserted)
SET @. PREVIOUSUSER = (SELECT UserID from Deleted)
And somebody better be minding the blind dude. Who's turn is it today, anyway? 'Cause nobody made me breakfast.