In my last article, we covered most of the basics of Sybase trigger design and behavior. However, there are still a few topics we need to touch on for the introduction to be complete. We’ll examine database settings for recursive/nested triggers as well as several other topics in this article. Since it’s the second article in the series, I hope to give a running start to anyone who thinks triggers are a necessary part of a comprehensive database design.
If a trigger performs a DML (data modification language) statement on another table and that table has a trigger defined for that action (INSERT, UPDATE or DELETE), then that trigger will fire as well. This is called “nesting,” and it can occur up to 16 levels deep. The nesting level can be derived from within any trigger via the @@nestlevel variable. The ability to nest triggers can be turned on and off with the Allow Nested Triggers configuration parameter.
If a trigger calls a stored procedure, which in turn performs a DML statement on the same table and recursive triggers have been enabled, this will probably lead to an infinite loop in which the nesting level limit will be exceeded and the trigger will fail. Recursive triggers will be covered in the next section.
If a Rollback Trigger is encountered while in a nested trigger (or the first trigger, for that matter) then all triggers called from the first one (as well as the initial DML statement) are rolled back as well. A Rollback Transaction will roll back everything to the outermost transaction.
The level of nested triggers and/or stored procedures can be determined by accessing the @@nestlevel global variable. Triggers and stored procedures can both invoke each other, and subsequently both will affect the @@nestlevel value.
If a trigger executes either a DML statement on its own table or calls a stored procedure, or even another trigger that does this, the initial trigger may refire. This is also called “nesting,” and by default, this is turned off in Sybase. However, the System Administrator (SA) can turn this on with the Allow Nested Triggers configuration option.
It isn’t easy to cause self-recursion accidentally. Not only must the Allow Nested Triggers configuration option be set by the SA, but the Set Self_Recursion On option must be set as well. If this option is set outside of a trigger, it stays set for the duration of the client session. If the option is set from within a trigger, it will remain in scope only for that trigger. If that trigger calls another trigger, the Self_Recursion option again defaults to off. When the trigger that set the option ends, the option reverts back to the default setting of off as well.
Inserted and deleted tables
In the last article we mentioned the “inserted” and “deleted” tables. We’ll explore those in more depth here. During the execution of a trigger, the inserted and deleted tables are available from the transaction log. They have no owner and need only be specified by their table names “inserted” and “deleted.”
Listing A is an example of an insert trigger that checks to make sure that a foreign key has an appropriate primary key match. The discounts table has stor_id as its foreign key and maps to the primary key of the stores table.
In the above example we do a join on the inserted table for the discounts table and the existing records in the stores table based on the stores stor_id primary key. If we don’t get a match equal to the number of records being inserted (as would be the value in the @@rowcount global variable) then we know that at least one of the stor_id values in the inserted table does not have a corresponding value in the stores table. To protect referential integrity, we then cancel the entire transaction, which rolls back all changes up to the beginning of the outermost transaction.
You may note that we check to make sure that @@rowcount != 0 so we can avoid the overhead of the rest of the trigger if no rows were actually inserted. This can happen as a result of a multirow insert for which no rows were satisfied in the WHERE clause.
You may also note that we assign the value of @@rowcount to a variable upon entry to the trigger. Remember that the @@rowcount global variable holds the affected number of rows for the last SQL statement only and is reset by all SQL statements except the DECLARE statement. Therefore, if you want to use it reliably in a stored procedure or a trigger, you should always assign it to a variable immediately upon entering the trigger.
The preceding example of a restricted insert can be applied to deletions of primary keys where matching foreign keys exist in other tables as well.
The following is an example of a cascaded deletion. This should be done with care, however, and you will probably want to make sure you archive rows from the foreign key tables for historical purposes before you execute the DELETE statement. The trigger could take care of this as well, but that is for advanced users and outside the scope of this article.
In Listing B we show the “cascaded deletion” abilities of triggers. In this case you would probably want to store the deleted records in a historical table. This could be accomplished by adding triggers for deletion on the appropriate tables and inserting the records from the deleted table for those base tables into a historical table (as shown in Listing C).
Note that the last column (in Listing C), deleted_by, which doesn’t exist in the base tables but which would probably be desirable in the archive tables, is used to track who actually made the deletion. This is a common methodology for an audit trail, especially for deleted data.
In my last article, “Sybase triggers protect your data,” I outlined some basic restrictions to triggers. Here are some additional restrictions you should be aware of.
- · You cannot create triggers on system tables.
- · In a trigger, you cannot access text or image columns on the inserted or deleted tables.
- · If you are using Component Integration Services, the trigger on a proxy table cannot access the inserted or deleted tables because the transaction log where those tables are located is on the remote server.
Are you trigger-happy yet?
We’ve now covered most of the basics of trigger design and development. In our next article we’ll look at keeping derivative values in a summary table in sync with the detail table. We’ll also cover updating multirow primary keys in a base table and how to change the foreign key references in related tables.