troubleshooting Question

SQL Error when trigger fires in SQL Server 2008 R2

Avatar of alexisbr
alexisbr asked on
Microsoft SQL ServerSQL
6 Comments2 Solutions103 ViewsLast Modified:
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

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

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
ASKER CERTIFIED SOLUTION
Vitor Montalvão
IT Engineer
Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros