In the third article of this series, I’ll cover maintaining summary values on the INSERT, UPDATE, or DELETE operations of a detail table. I’ll also cover cursors in a trigger.
For this series, we’ve been using tables in the pubs2 database that ships with Sybase ASE. If you don’t see or can’t use this database in your setup, you should talk with your DBA. Most third-party manuals, tutorials, and even the Sybase ASE manuals themselves refer to this database.
The model of this database is quite simple; see Figure A.
Did you miss an article?
Maintaining derivative values
For examples of maintaining derivative values across tables using triggers, we’ll define INSERT, UPDATE, and DELETE triggers for the salesdetail table. The first thing we’ll do is define a DELETE trigger to modify the total_sales column (see Listing A).
The DELETE trigger is pretty straightforward. We simply set the total_sales column in the titles table minus the sum of the qty column in the deleted table. The key here is to use the group by clause for a multirow deletion. We check for a single-row deletion so we can avoid the overhead of the group by. This is especially important in a heavy-traffic database.
Now let’s go for the INSERT trigger. The code is presented in Listing B.
The first thing we do is make sure that at least one row was affected by the SQL statement that fired the trigger. If not, then why bother continuing? We simply return from the trigger. If so, we next check to make sure that all the title_ids exist in the titles table and the stor_id + orn_num combinations exist in the sales table.
Then we get down to the derivative update portion of the trigger. We check to see if only one record has been affected, and by the act of checking we increase the total_sales value by the value in the record. For a multirow INSERT, we once again use the group by to update the total_sales in title.
Listing C includes the code for the UPDATE trigger.
The update trigger is the most complex for derivative values in that it accesses both the inserted and deleted tables. This could actually be done in one SQL statement by joining the inserted and deleted tables to the titles table and updating the total_sales column with a calculation, but this simplifies the logic and makes the process a little more readable for the purposes of this discussion.
Derivative values in other tables are something that most developers shy away from at first, but they are pretty simple if you understand triggers at the outset.
Cursors in a trigger
Something we haven’t mentioned in this series on Sybase triggers is cursors. Cursors may be declared within a trigger. However, if the cursor accesses the deleted or inserted tables, then the cursor is only accessible inside that trigger. That is, if you call a stored procedure or fire another trigger from within a trigger that declares a cursor and the cursor accesses the inserted or deleted tables, then the stored procedure or nested trigger can’t see the cursor. Otherwise, it’s possible to access this cursor as long as the trigger that declared it stays in scope.
One consideration when deciding if a cursor is necessary in a trigger is performance. Triggers have very little overhead as they are cached, and although not re-entrant—i.e. two sessions firing the same trigger will cause the trigger to be loaded in memory twice—they are quite efficient. The query plan for a trigger is cached in the same way as a stored procedure, and a cursor severely degrades this efficiency. In my experience, there have been few instances where result set (set-based) processing couldn’t be used in place of cursor (row-based) processing; so to prevent severe performance degradation, I avoid cursors in a trigger at all cost.
Keep an eye on performance trade-offs when using triggers
In this article, we maintained a derivative column based on modifications to another table. This common trigger function is used when detail tables are not too heavily updated but contain many rows. The methodology facilitates deriving a summary value from a single row in a table rather than having to sum all the values in a detail table, which can take longer than the developer wants users to wait. There is a trade-off between the time it takes to update INSERT, UPDATE, or DELETE records and the time it takes to report or show the summary info on-screen, so take this into consideration. However, the query-plan for the trigger stays cached, as opposed to the SQL statement required to sum this information, which needs to be recompiled every time it is submitted to the database server. A developer needs to keep an eye on performance when using triggers of this type and decide whether it is worth the additional update overhead.
We’ve seen how maintaining summary values with triggers can make a database administrator’s life a little bit easier. Used wisely, triggers can automate many tedious tasks.