We help IT Professionals succeed at work.

SQL Error when trigger fires in SQL Server 2008 R2

alexisbr
alexisbr asked
on
94 Views
Last Modified: 2017-03-22
Hi.  I am trying to use triggers to automatically update LastUpdated (datetime) and LastUpdatedBy (varchar(20).  

I wrote this trigger for LastUpdated and it works fine.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trEmailGroupMembersLastUpdated] ON [dbo].[EmailGroupMembers]
FOR UPDATE 
AS
UPDATE EmailGroupMembers SET EmailGroupMembers.LastUpdated=getdate()
FROM EmailGroupMembers INNER JOIN Inserted ON EmailGroupMembers.EmailGroupMemberID= Inserted.EmailGroupMemberID

Open in new window


However, the trigger I wrote for LastUpdatedBy does not work.  
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trEmailGroupMembersLastUpdatedBy] ON [dbo].[EmailGroupMembers]
FOR UPDATE 
AS
UPDATE EmailGroupMembers SET EmailGroupMembers.LastUpdatedBy=(ltrim(rtrim(substring(suser_sname(),(5),len(suser_sname())))))
FROM EmailGroupMembers INNER JOIN Inserted ON EmailGroupMembers.EmailGroupMemberID= Inserted.EmailGroupMemberID

Open in new window


I get this error from the Access 2010 frontend form when it tries to save to SQL Server.  
sql error when trigger fires
I have tried different ways to write the trigger but I cannot avoid this error.  Does anyone know what I am doing wrong?

Thanks,
Alexis
Comment
Watch Question

IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
ste5anSenior Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you both!  I used SET NOCOUNT ON and the trigger you wrote.  It never occurred to me to combine them. That is brillant!

It's working now.  I thought I had already tried adding SET NOCOUNT ON but I guess not.

Also, I was using the functions to limit the size of the username field since our domain is included if I just use suser_sname() but I just increased that field to cover longer user names.

Thank you again!

Regards,
Alexis

Author

Commented:
Thank you again!
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
When using SUSER_SNAME() redefine your LastUpdateByColumn. The data type of SUSER_SNAME() is SYSNAME (NVARCHAR(128)).

Author

Commented:
Thank you!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.