triggers

Question

Post reply

triggers

hollymatrix

SSC Rookie

Points: 30
More actions
December 16, 2008 at 4:24 pm

#73162

hi there
this is my problem
i have a trigger defined on a table.
the code executed by this trigger is to check a column from the one in the (inserted or deleted tables) and then in my update that column with a default value in the originale table
for example
create trigger tr_name
on [my_table]
for update
as
update [my_table]
set [row_uid] = newid()
from [deleted] A
where A.[UID]=my_table.[UID]
in this case i have this trigger calling himself until it reashes 32 times, and i get an error.
i'm sure that the option recursive triggers is set to off for my DB
so what is wrong with this, or is there an other method to get the same result (update a column in the updated row from within the trigger)
thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

What is your value for "nested triggers" option (ON or OFF)

Are they any other triggers on this table which update the table?

You might want to check BOL (Nested triggers) for SQL 2000 at:

http://msdn.microsoft.com/en-us/library/aa214644(SQL.80).aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

ps. One Orange Chip Points: 29423 More actions · Answer 2

somewhat similar issue was posted earlier. You might want to refer that as well...

http://www.sqlservercentral.com/Forums/Topic618626-149-1.aspx#bm618684

Pradeep Singh

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Just don't update what does not need to be updated:

update [my_table]

set [row_uid] = newid()

from [deleted] A

where A.[UID]=my_table.[UID]

and my_table.[row_uid] IS NULL

_____________
Code for TallyGenerator

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Sergiy (12/16/2008)
Just don't update what does not need to be updated:
update [my_table]
set [row_uid] = newid()
from [deleted] A
where A.[UID]=my_table.[UID]
and my_table.[row_uid] IS NULL

Assuming that my_table.UID is unique then the my_table.row_id Is Null should be unnecessary since there should only be 1 row affected per UID affected by the original update.

Either Nested triggers or recusive triggers must be ON because it should not fire itself otherwise.

To be honest, I actually looked at this one because I assumed the trigger would not handle sets, but it does so I am pretty happy.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

hollymatrix SSC Rookie Points: 30 More actions · Answer 5

thans for your replays (all of you)

bitbucket (12/16/2008)
What is your value for "nested triggers" option (ON or OFF)
Are they any other triggers on this table which update the table?
You might want to check BOL (Nested triggers) for SQL 2000 at:
http://msdn.microsoft.com/en-us/library/aa214644(SQL.80).aspx

the recursive triggers option on my database is off.

i don't want to change the option on the server (because maybe there is a database witch needs the recursive option)

(i'm using SQL Server 2005)

an other question :

is there a difference between nested triggers and recursive triggers ?

thanks