March 27, 2007 at 8:04 am
I am trying to get a trigger to fire after someone has made an update to a table. I want the trigger to update a column value in the table called, “FinishTime.” I wanted to use the getdate() function to insert the date and time in the “FinishTime” column upon completion of the user has made an update to the table. But, only if the value of another column is equal to ‘InProgress’.
Here is the code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [trUPD_Table1_FinishTime] ON [dbo].[Table1]
AFTER UPDATE
AS
UPDATE dbo.Table1
SET FinishTime = getdate()
FROM INSERTED I
WHERE Table1.RunID = i.RunID And Table1Status.Stutus = 'InProgress';
--Any help would be appreciated
--Thanks
March 27, 2007 at 11:37 am
Where clause mistake, should read:
WHERE Table1.RunID = i.RunID And Table1.Stutus = 'InProgress';
March 27, 2007 at 11:47 am
Looks fine to me and I tested a similar setup with no issues. What is happening that is not as you expect?
March 27, 2007 at 12:36 pm
Well, nothing had happened because I was leery of executing the trigger, first trigger and all. Thanks for the advice, I put it in play and now I will wait and see how it goes.
March 27, 2007 at 12:47 pm
I would set up a test scenario on another server and make sure you get the condition you want to have happen first. (QA the code)
March 28, 2007 at 7:17 am
Thanks for the advice, the test yielded an error:
Msg 217, Level 16, State 1, Procedure trUPD_Table1_FinishTime, Line 5
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Is it looping on "InProgress"? Do I need a 'IF UPDATE ()' for the 'Status' column?
March 28, 2007 at 7:35 am
Looks like direct recursion and you have the RECURSIVE_TRIGGERS option set
As you stated, use 'IF UPDATE ()' to control the update in the trigger
or use TRIGGER_NESTLEVEL
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply