can anyone help me

i've created a trigger on one of my tables (called PROJECT), that I beleve should fire if I either add a new record to the table, or update an existing record.

The trigger is working fine if I update the record, but its not firing when i create a new record, and I dont understand why,

This is my trigger

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'TR_TrackUsers' AND type = 'TR')
   DROP TRIGGER TR_TrackUsers
GO


CREATE TRIGGER TR_TrackUsers
ON project
FOR INSERT, UPDATE
AS

DECLARE @PROJECTID BIGINT,
@USERID INT,
@NewUserID int

SELECT @PROJECTID = i.ProjectId,
   @USERID = i.UserID
FROM inserted i

SELECT @NewUserID = d.UserID
FROM deleted d


if @USERID <> @NewUserID

    BEGIN
    INSERT INTO
    ALLOCATIONLOG
    (ProjectID, UserID )
    VALUES
    (@PROJECTID, @USERID)

If you add a new record then @NewUserID will be NULL as DELETED is empty. Comparing NULL with an ID will give NULL not FALSE. Try adding OR @NewUserID IS NULL to the test.
You could also LEFT JOIN the INSERTED table to the DELETED on UserID and select the row WHERE DELETED.UserID IS NULL to get only the new record. (This trigger will work badly if you ever do a multiple row update.)

Why do you bother with having the trigger fire for updates aa then INSERTED.UserID will equal DELETED.UserID and nothing will happen, or is there more to the trigger that you haven't shown?

--

Joe (Microsoft MVP - XML)

Brilliant, Thanks very much Joe

Debbie