November 27, 2006 at 10:00 am
I need to update a tables modified_date column when a record is updated.
If I do it in the AFTER UPDATE, won't the trigger be called again, getting it into a loop?
Any examples would be great.
thanks,
Dean
November 27, 2006 at 10:26 am
What do you mean the "tables modified_date".
is this a data value in a different table?
or are you talking the create date shown in enterprise manager?
or is this a field that shows the Records modified date?
November 27, 2006 at 10:28 am
This is a field/column in the same table called Modified_Date
November 27, 2006 at 10:30 am
And why can't u add Modified_Date to your SQL Statments?
November 27, 2006 at 10:51 am
it can be but looking for this type of solution for other reasons.
Is there a way of having the trigger some how use the Instead of command to handle not firing the trigger twice and still updating the modified field as well.
November 27, 2006 at 11:38 am
The trigger will not fire twice, it will only fire once.
create trigger foo
on mytable
after update
as
update A
set A.modified_date = getdate()
from mytable A
join Inserted I on A.id = I.ID
GO
NOT TESTED
November 27, 2006 at 11:49 am
Will this be an issue if I am within a Transaction?
November 27, 2006 at 11:55 am
Won't this cause a recurring action on the trigger??
November 27, 2006 at 1:13 pm
You'll need to set RECURSIVE_TRIGGERS off
By definition this would be a recursive trigger, so it will not work if you require recursive triggers to be set to on because of some other reason in the database.
This will not effect the transaction unless something fails, causing everything to rollback.
Recursive Triggers
A trigger does not call itself recursively unless the RECURSIVE_TRIGGERS database option is set. There are two types of recursion:
Occurs when a trigger fires and performs an action that causes the same trigger to fire again. For example, an application updates table T3, which causes trigger Trig3 to fire. Trig3 updates table T3 again, which causes trigger Trig3 to fire again.
Occurs when a trigger fires and performs an action that causes a trigger on another table to fire. This second trigger causes an update to occur on the original table, which causes the original trigger to fire again. For example, an application updates table T1, which causes trigger Trig1 to fire. Trig1 updates table T2, which causes trigger Trig2 to fire. Trig2 in turn updates table T1 which causes Trig1 to fire again.
Only direct recursion is prevented when the RECURSIVE_TRIGGERS database option is set to OFF. To disable indirect recursion, set the nested triggers server option to 0, as well.use pubs
drop table test
create table test (pk int identity, value varchar(100), Modified_Date datetime default('1/1/1900'))
insert into Test (value)
values ('Foo')
create trigger foo
on test
after update
as
update A
set Modified_Date = getdate()
from Test A
join inserted I on a.pk = i.pk
GO
update test
set Value = 'bar'
where pk = 1
select *
from test
Drop table Test
November 27, 2006 at 1:17 pm
How would you set RECURSIVE_TRIGGERS off?
November 27, 2006 at 1:38 pm
EXEC dbo.sp_dboption 'DbNameGoesHere', 'recursive triggers', 'OFF'
November 28, 2006 at 9:14 am
you can visually check db settings in Enterprise manager.
Right Click Database, Properties, Options tab.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply