SQL Server 2005 - Trigger not firing

I have two tables User & User Log . User log table basically logs all the changes(insert/update/delete) made to user table.

I have a trigger on User table which is as below:

ALTER TRIGGER [dbo].[TRG_UserLog]
   ON  [dbo].[Users]
   FOR INSERT,UPDATE,DELETE
AS 
-- Declare variables here
DECLARE @UserName  VARCHAR(50)
DECLARE @FirstName  VARCHAR(50)
DECLARE @LastName       VARCHAR(50)
DECLARE @Email  VARCHAR(50)
DECLARE @RoleID   INT
DECLARE @UpdatedBy VARCHAR(50)

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here

    IF @@ROWCOUNT = 0
    BEGIN
        RETURN
    END


    IF EXISTS(SELECT * FROM INSERTED)
        --INSERTED / UPDATED
        BEGIN
                SET @UserName   = ( SELECT UserName FROM INSERTED)
                SET @FirstName  = ( SELECT FirstName FROM INSERTED)
                SET @LastName   = ( SELECT LastName FROM INSERTED)
                SET @Email      = ( SELECT Email FROM INSERTED)
                SET @RoleID     = ( SELECT RoleID FROM INSERTED) 
                SET @UpdatedBy  = ( SELECT ModifiedBy FROM INSERTED)
                INSERT INTO UserLog(UserName,FirstName,LastName,Email,RoleID,[DateTime],UpdatedBy)
                    VALUES (@UserName,@FirstName,@LastName,@Email,@RoleID,GETDATE(),@UpdatedBy)
        END
    ELSE
        -- DELETED
        BEGIN
                SET @UserName   = ( SELECT UserName FROM DELETED)
                SET @FirstName  = ( SELECT FirstName FROM DELETED)
                SET @LastName   = ( SELECT LastName FROM DELETED)
                SET @Email      = ( SELECT Email FROM DELETED)
                SET @RoleID     = ( SELECT RoleID FROM DELETED) 
                SET @UpdatedBy  = ( SELECT ModifiedBy FRO开发者_高级运维M DELETED)
                INSERT INTO UserLog(UserName,FirstName,LastName,Email,RoleID,[DateTime],UpdatedBy)
                    VALUES (@UserName,@FirstName,@LastName,@Email,@RoleID,GETDATE(),@UpdatedBy)
        END
END

The above triigger is not inserting data in userlog table when insert/update or delete is made to user table. Is there anything wrong with the code?

You should not be setting variables from INSERTED and DELETED. These "tables" could potentially hold more than 1 row in them and using set is going to lose data. Do this instead (on both the INSERT and DELETE blocks):

INSERT INTO UserLog(UserName, FirstName, LastName, Email, RoleID, [DateTime], UpdatedBy)
SELECT UserName, FirstName, LastName, Email, RoleID, getdate(), ModifiedBy
FROM INSERTED

If you make that change, you don't even need the IF/ELSE blocks, since it'll only INSERT/DELETE based on whether or not there are rows in those tables.

Also, the check on @@ROWCOUNT is unnecessary. You should be able to remove that completely.

This will always be true:

SET NOCOUNT ON;   -- this sets @@ROWCOUNT to 0

IF @@ROWCOUNT = 0
BEGIN
    RETURN
END

because calling SET NOCOUNT ON; affects @@ROWCOUNT, and since no rows are affected by that statement, it's set to 0. So the rest of the code never runs. At the very least, you would run that code before SET NOCOUNT ON; (or store @@ROWCOUNT in a variable for later use), but as indicated by the other answer, it's completely unnecessary anyway.