June 20, 2003 at 12:42 pm
Hello,
I have a Trigger set up that is supposed to update the current time value in a field called 'Updated' which is of type 'datetime'. My intent is to have this field updated with the current time that a record is updated.
The trigger looks like this:
***********************************
CREATE TRIGGER TR_SetUpdateDate
ON tblSBStreetData
FOR UPDATE
AS
IF EXISTS
(SELECT 'True'
FROM Deleted DL
LEFT JOIN tblSBStreetData SB
ON DL.RecordNum=SB.RecordNum
)
BEGIN
UPDATE tblSBStreetData
SET Updated = GETDATE()
WHERE EXISTS (SELECT DL.Recordnum from Deleted DL
LEFT JOIN tblSBStreetData SB
ON DL.Recordnum=SB.Recordnum)
END
********************************
When I attempt to Insert or Update records into this table, I get the following message:
Server: Msg 217, Level 16, State 1, Procedure TR_SetUpdateDate, Line 12
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
New records are not inserted, and current records are not updated.
What must I do to update the 'Updated' field with the current time when a record is edited?
Thanks for your help!
CSDunn
June 20, 2003 at 12:54 pm
CSDunn,
Remember to check that the field you are updating has changed. Perhaps on the EXISTS clause add the criteria of DL.Updated <> SB.Updated
Guarddata
June 20, 2003 at 4:19 pm
Thanks, that's what I was missing.
CSDunn
quote:
Remember to check that the field you are updating has changed. Perhaps on the EXISTS clause add the criteria of DL.Updated <> SB.Updated
June 23, 2003 at 1:07 am
Look like you ran into a loop. Trigger triggers itself until 32-level nesting limit is exceeeded. Have a look at "TRIGGER_NESTLEVEL" in BOL, check out the sample and set trigger to return if nesting > 1.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply