April 19, 2002 at 12:23 pm
The following trigger ALWAYS updates approvedate, even when approvedby is not updated. Any idea why? I'm using SQL 6.5
if update(approvedby)
begin
update itemappr
set approvedate = getdate()
where reqnum = @reqnum
end
Thanks for your help
Paul
April 19, 2002 at 12:36 pm
Do a trace on the DB activity with an update query that should not cause the trigger to fire.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 19, 2002 at 12:57 pm
The trace shows a select statement where data is gathered from every column in the table, then an update statement where every column, including the approvedby column, is updated. Therefore, every column is being updated every time. Do I have something set wrong?
Paul
April 19, 2002 at 1:16 pm
is the value being changed? I wouldn't be surprised if v6.5 didn't flag update() with no actual data changes.
Steve Jones
April 19, 2002 at 1:27 pm
That trigger fires even when the approvedby value is NOT changed. I found a work-around, but would rather fix it right. The work around is to use a second column and compare the value of the first column to that of the second. If those values are equal, leave the date column alone; otherwise, change the date column and update the second column to equal the first. Confusing? It works.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply