Share
ReportI am writing a trigger for GP that will fire when ever the RMA status of a warranty changes and will put the current date onto an extender field. Though in testing I keep getting a "Database Constraint violated". I know I do not check to see if the extender row exist so I am only testing this on records I have manually created so far.
I have commented out all the statuses changes except for Quote Provided.
Thanks in advance
ALTER TRIGGER [dbo].[InternalRepairDates]
ON [dbo].[SVC05000]
AFTER update
AS
declare @id varchar(20)--this is to hold the RMA number
set @id = (select RETDOCID from inserted)-- dont actually use this but good to have.
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if UPDATE(retstat)-- this is for when only this column changes
if (select inserted.RETSTAT from inserted) = '30Q' -- this is the code for Quote Provided
--if exist
update EXT01102
set DATE1 = getdate()
--select *
from EXT01100
join EXT01102
on EXT01100.Extender_Record_ID = EXT01102.Extender_Record_ID
join inserted
on inserted.RETDOCID = EXT01100.Extender_Key_Values_1
where Extender_Window_ID = 'INTERNALREPAIR' --this is the unique identiqier for the extender window
and Field_ID = '802' --this is the code for the window of the field for Quote Provided
and inserted.RETDOCID = EXT01100.Extender_Key_Values_1
--end
/*
--same as above except for the field for approved date
else if (select i.RETSTAT from inserted i) = '30A' -- this is the code for Quote Approved
--if exist
update EXT01102
set DATE1 = getdate()
--select *
from EXT01100 join EXT01102
on EXT01100.Extender_Record_ID = EXT01102.Extender_Record_ID
join SVC05000 on SVC05000.RETDOCID = EXT01100.Extender_Key_Values_1
where Extender_Window_ID = 'INTERNALREPAIR' --this is the unique identiqier for the extender window
and Field_ID = '803' --this is the code for the window of the field for Quote Approved
and SVC05000.RETDOCID = Ext01100.Extender_Key_Values_1
and exists(Select 1 from inserted i where i.RETDOCID = SVC05000.RETDOCID)
--same as above except for the field for decline date
else if (select i.RETSTAT from inserted i) = '30D' -- this is the code for Quote Declined
--if exist
update EXT01102
set DATE1 = getdate()
--select *
from EXT01100 join EXT01102
on EXT01100.Extender_Record_ID = EXT01102.Extender_Record_ID
join SVC05000 on SVC05000.RETDOCID = EXT01100.Extender_Key_Values_1
where Extender_Window_ID = 'INTERNALREPAIR' --this is the unique identiqier for the extender window
and Field_ID = '804' --this is the code for the window of the field for Decline Offer
and SVC05000.RETDOCID = Ext01100.Extender_Key_Values_1
and exists(Select 1 from inserted i where i.RETDOCID = SVC05000.RETDOCID)
*/
-- Insert statements for trigger here
--end the first if
END--end the update statement
*This post is locked for comments
I have the same question (0)
All responses (
Answers (
2
