By Kaye Garten
These tips originally appeared in the Builder.com SQL Server e-newsletter.
Sometimes, triggers can inadvertently fire other triggers or even the same trigger recursively. In this compilation of SQL Server tips, you’ll learn to detect such an event. Then, by changing gears a bit, you’ll see how to read the backup information stored in SQL Server’s msdb database.
Return the value of nested trigger levels
The TRIGGER_NESTLEVEL() function, as well as the @@NESTLEVEL global variable, will return the nested trigger level, which is an attribute that can be used several ways. When a transaction initiates a trigger, which in turns initiates an implicit transaction that will initiate another trigger, you have just created two trigger levels within the transaction.
There have been published designs for database lookup tables that have one main table with which the application or front end interfaces. This allows for one object instantiation for many lookup tables instead of having multiple objects instantiated for all the lookup tables. The one main table has triggers that will maintain the data for all the lookup tables.
In such a database design, the TRIGGER_NESTLEVEL() function or @@NESTLEVEL global variable can prevent users from entering data directly into the lookup table and bypassing the main table that maintains all the lookup tables.
Another use of the function or global variable is for breaking a recursive trigger. There may be a situation where a trigger will initiate DML that will initiate the current trigger again. You can check the nested trigger level to break out of this recursive trigger situation.
Listing A contains a sample script that demonstrates a child table not allowing direct inserts into the table.
Find dump device and backup info in the msdb database
Files recognized and managed by SQL Server that contain database backup information are called .Dump devices. You can create the dump device and then back up databases to it. You can also back up master, model, and msdb to the same dump device. In addition, you can use SQL Server Enterprise Manager (SEM) to administer the devices and show information about what was backed up, when it was backed up, and the size of the backup.
You can use the tables backupset and backupmediaset in the msdb database to find the information on database backups as well. Backupmediaset identifies the dump device where the table backupset categorizes the database backups on the dump device. Even when the backup disk files are deleted, the information is still retained in the tables. This is an excellent source to find out the history on backups even when the backup files aren’t present.
Listing B shows a sample script illustrating the information being tracked in backupmediaset and backupset in the msdb database.
Before running this script, make sure you have enough disk space to back up the master, model, and msdb databases on the C: drive. Use this script on a development server and not a production server. Be certain you have backups available for the master, model, and msdb databases for recoverability other than the C:\SystemDump.BAK file created by the script.