Avatar of skull52
skull52
Flag for United States of America asked on

Error when creating an UPDATE Trigger in SQL

I am trying to create a trigger on a table (RM00102) when Column (USERDEF1) is updated so it will add the current date to Table (SSGRM001) column (CustUpdDate). but when I fire the trigger with a change to USERDEF1 i get the following error
"a save operation on table RM_Customer_MSTR_ADDR failed because a database constraint was violated"

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[trg_SSGRM001_CUSTUPD] ON [dbo].[RM00102] 
	AFTER UPDATE 
AS 
set nocount on 
IF UPDATE(USERDEF1)
BEGIN 
UPDATE D
SET D.CustUpdDate = GETDATE() 
FROM   RM00102 A INNER JOIN
SSGRM001 D ON A.CUSTNMBR = D.CUSTNMBR 
END 
set nocount off
GO

Open in new window

Microsoft SQL ServerMicrosoft Dynamics

Avatar of undefined
Last Comment
skull52

8/22/2022 - Mon
JesterToo

That error message does not appear to originate from this trigger... what is "RM_Customer_MSTR_ADDR" and how does it relate to this transaction?
skull52

ASKER
"RM_Customer_MSTR_ADDR"  is the name used in Dynamics GP for table RM00102
ASKER CERTIFIED SOLUTION
JesterToo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
skull52

ASKER
I was using SET D.CustUpdDate = GETDATE() but should have been using
 SET SSGRM001.CustUpdDate = (SELECT  DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AS Expr1)
I missed that.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Scott Pletcher

Or just CAST it as date:

SET D.CustUpdDate = CAST(GETDATE()  AS date)
skull52

ASKER
Scott, Yeah... that would be less complicated, thanks