|
I assume you are refering to trigger types that exist both in the per row and per statement mode, i.e. you are not refering to BEFORE triggers but to AFTER and INSTEAD OF triggers. It's obviously that both types will differ in performance when the triggering statement operates on more than one row, just as a per row trigger is executed once per row and a statement trigger only once for the whole statement. Accordingly, I would assume that the parts of the trigger logic that have to check whether the trigger has to be fired at all (say, checks for the "update of" list or the "when" clauses) will be more efficient when checked for the whole statement. However, it's hard to tell what the real performance implications are (and if they are significant at all) as that will usually depend on the action in the trigger. I usually prefer to use per row triggers
Just my 2 cents... |
|
Table design is a contributing factor along with database operation issues (as in cascades of updates of one or more columns through multiple tables) in making an appropriate choice of which type of trigger to use. I would postulate that while row triggers are easier to code they may be more costly in terms of execution because they are invoked more often if your database cascades updates of one or more columns across multiple tables you should also consider that cascading row triggers do not have the benefit of updated indicies on the tables that triggers are cascaded from (where per statement triggers do have that benefit) which can causes some problems especially where recursive table designs (tables where a foreign key in a table references the primary key in the same table) are present. Volker's answer would probably provide acceptable performance for normal business applications where a cascading columns do not cascade across multiple tables. However I believe you may find statement triggers more efficient when cascade updates frequently or have recursive tables where having updated indicies to allow other triggers to operate successfully are required. Statement level triggers do have the additional requirement of a (non-changing often) called surrogate key if not already available in the table to link the rows in the table to the inserted and or deleted rows, as appropriate, provided to drive the actions required of the statement level trigger. 1
Well, AFAIK SQL Anywhere's builtin DRI support (UPDATE/DELETE CASCADE, SET NULL, SET DEFAULT) is generally implemented as row level system triggers. That's not a counter-argument against your reasons, but it's just implemented that way (and has these performance issues you tell about). However, I am sure they will only be of importance when very high throughput is necessary. - In that case, a lot of "comfortable" SQL features might be quite expensive...
(22 Apr '11, 08:44)
Volker Barth
Replies hidden
I agree with your points here, too. including the assessment that DRI support (generated system triggers) are row level triggers. My personal design strategy places considerable application / functional code sometimes referred to as business rules into my database triggers. My application deals with a meta data layer to describe the actual data and annual changes to its collection. As a result there are far more than the average number of redundant columns between tables (possibly too many according to some people). With my chosen design strategy often the additional complexity of on statement trigger is mitigated because the functional code can be simplified using the knowledge that the indicies for the other tables always reflect the current key values for all rows. Using this philosophy I use on row triggers only to select data from other tables but limit updates to the row of the table that fired the trigger. This also prevents me (as a personal design decision only) from using the DRI support you mentioned (that generates system triggers) forcing all such updates into manually coded on statement triggers. All cascade type updates triggered from one table destined for another table or even recursive linked rows in the same table are coded as on statement triggers in my design. Adopting a strategy like mine is probably not wise or desirable for the vast majority of databases dealing with traditional business applications, but sometimes it may be necessary.
(22 Apr '11, 12:51)
pasha19
You mean per-row triggers are fired before the index is updated in contrast to per-statement triggers?
(28 Apr '11, 03:37)
Martin
simply yes -- for the table the row triggers are fired from.
(28 Apr '11, 20:52)
pasha19
|
|
Per row triggers fire when each row is updated. The SQL statement for the table that fired the per row trigger is not complete so any index updates have not been made in that table. The index updates for that table occur after the entire sql statement completes which is after all row triggers have completed their processing. |
