Trigger: How do I get the User who made the change

This is my first time on EE - I gave this 300 points - cause I REALLY need the answer, but I am sure its not very hard!

I need a trigger that will update  who entered/changed the data and when.  The trigger belwo works until I added the SUser_name part.  What am I doing wrong?


ALTER   TRIGGER dbo.tblUnion_TrackUpdates
ON dbo.tblUnion
FOR INSERT, UPDATE
AS
DECLARE @insert_count AS INT
DECLARE @delete_count AS INT

SELECT @insert_count = (SELECT COUNT(*) FROM inserted)
SELECT @delete_count = (SELECT COUNT(*) FROM deleted)
      
IF (@insert_count >=1) and (@delete_count = 0)
      BEGIN
      Update tblUnion
               set chrCreatedBy = SUser_Name
                  set dtmCreatedWhen = GetDate()
      From tblUnion, Inserted
      where tblUnion.intUnionID = Inserted.intUnionID
      END
IF (@insert_count >=1) and (@delete_count >=1)
      BEGIN
        Update tblUnion
            set chrModifiedBy = SUser_Name
               set dtmModifiedWhen = GetDate()
         From tblUnion, Inserted
         where tblUnion.intUnionID = Inserted.intUnionID
      END

Thanks Kim
KimberleyYAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
Syntax error in the UPDATE statement; use only ONCE the SET opertator

IF (@insert_count >=1) and (@delete_count = 0)
     BEGIN
     Update tblUnion
             set chrCreatedBy = SUser_Name
             , dtmCreatedWhen = GetDate()
     From tblUnion, Inserted
     where tblUnion.intUnionID = Inserted.intUnionID
     END
IF (@insert_count >=1) and (@delete_count >=1)
     BEGIN
       Update tblUnion
          set chrModifiedBy = SUser_Name
           , dtmModifiedWhen = GetDate()
        From tblUnion, Inserted
        where tblUnion.intUnionID = Inserted.intUnionID
     END

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.