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 |
|
Utpal
Posting Yak Master
179 Posts |
 Posted - 2002-03-07 : 04:53:46
|
| Hi everybody, Is is possible to set off the nested triggers setting only for a particular database ? If I set off the setting for SQL server, it would set off the setting for other databases (of applications other than mine) as well, which is not desirable. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-07 : 08:10:27
|
| No, it's a server setting, not a database setting.For the database that could use nested triggers, you could always take the trigger code from the child tables and include it in the parent tables' triggers, as long as you modify it appropriately. |
 |
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-03-08 : 05:12:22
|
| But the problem would arise in the case where the database which requires the nested triggers setting is of not my application. So I couldn't modify it. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-08 : 07:41:43
|
| Look in Books Online at the @@NESTLEVEL variable. You could modify your other triggers to test @@NESTLEVEL and exit the trigger if it's greater than zero. I've never tested this myself but I'm pretty sure it's a possibility. |
|
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-03-08 : 23:58:45
|
| That would be great if it worked ! I'll give it a try. Thanks a lot Robvolk ! |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-03-09 : 01:29:35
|
Utpa, Remember this again will be a server setting. whatever the level you put on @@NESTLEVEL it will not be local to the database you are working on but on the whole Server(AFAIK)quote:
You could modify your other triggers to test @@NESTLEVEL
-------------------------------------------------------------- |
|
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-03-09 : 01:32:23
|
The @@NESTLEVEL variable is behaving very strangely. When I modify the current_stock column (Item_Master_current_stock table) from Enterprise manager it shows a value of 3 in the table's Update trigger, but when I update the column from Query Analyser, using UPDATE, it shows a value of 1.When the current_stock is updated indirectly (by the Item master update trigger) on updating the opening_stock (Item_Master table) from Enterprise manager, it shows a value of 4, but when I update the opening_stock from Query Analyser, using UPDATE, it shows a value of 2.Could you sort this out ? |
|
|
|
|
|
|
|
|
Nested triggers