Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jhilb
Starting Member
22 Posts |
 Posted - 2002-09-16 : 15:21:36
|
| How can I make sure that a particular trigger runs only once?A. is an update trigger on a table that updates some info.B. is an update trigger that sets the LastUpdate datetime to getdate()A runs, B runs, then A runs again (presumably because B just caused another update trigger to fire).How can I tell these triggers to run only once? |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-09-16 : 15:27:47
|
| You could do both in one trigger. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-09-16 : 15:33:39
|
| A worse idea is to look at @@nestlevelJay White{0} |
|
|
|
jhilb
Starting Member
22 Posts |
Posted - 2002-09-16 : 16:20:51
|
| Why is that worse?I am using:IF TRIGGER_NESTLEVEL() > 1 RETURNSeems to work, but should I not be? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-16 : 16:26:18
|
| The trigger(s) should only execute once, basically, nesting them is a bad idea. I'm of the opinion that there should be one trigger for UPDATE, one for INSERT, etc., instead of multiple triggers of the same type. An INSERT is one operation. An UPDATE is one operation. They're treated as individual transactions. That's how I think about/justify it anyway. In any case, multiple UPDATE triggers can't be designated to fire in a consistent order unless you're using SQL Server 2000, and even that is limited. It's one of those problems that isn't a problem if everything goes into a single trigger like Chad suggested.You might also want to check the RECURSIVE_TRIGGERS setting on your server. There's more about it in Books Online, it describes how to configure the server to avoid recursion problems. |
|
|
|
|
|
|
Getting a trigger to fire only once?