Trigger Disadvantages

Triggers are not without their disadvantages. Here we discuss the most important ones. One of the ways that we judge the quality of an application's physical design is to query the number, size, and types of database triggers that are incorporated into the application. If there are fewer than 10 database triggers, this usually indicates that an older application has been migrated from an Oracle6 environment. Fifty or more database triggers indicate functionality indecision by the users during the build phase and probable performance issues. A number between 10 and 50 leads us to believe that the application was carefully designed and developed for Oracle7 or above deployment. On the basis of the quality of the trigger code, we can usually judge the quality of the application. These observations are all rules of thumb and are occasionally incorrect, but they do help us to form an immediate opinion of an application before committing our time and energy to that application's future.

Not Compiled

Unlike stored procedures and packages, triggers are not held in the database in a compiled (PL/SQL pcode) form. Every time a trigger is fired, the code must be recompiled. However, overheads will be reduced if the trigger is already in the shared buffer pool. One alternative to this repetition is for each trigger to call a stored procedure to perform the work. The procedure is precompiled, and the trigger code is reduced to a handful of lines.For example:

New command: sql> alter trigger Summary_Emp_T1 compile;

Note: Oracle7.3/PL/SQL 2.3 and above supports precompiled PL/SQL trigger code.

No SELECT Trigger Support

Database triggers work only for DML operations. A trigger cannot be attached to a SELECT SQL statement. We have seen a number of instances in which an Access Validation trigger would have been invaluable on a business-sensitive table. This problem of restricting who can read what, where, and when can be handled via views, roles, and access grants, but it becomes very difficult when you are trying to retrofit a business enhancement. Allowing DML triggers for SELECT statements would not be practical (or even possible) with locking, rollback, and transaction state conflicts, but read-only triggers would be handy.

Complete Trigger Failure

When a database update fails, the offending statement and all previous trigger updates are also rolled back. Some programmers try to use data validation triggers to write an error message to a logging table before "failing" the transaction. They cannot understand why, when the transaction is aborted, no error log is found in the logging table. What has really happened is that the error message has been written to the logging table and then immediately rolled back by the very same trigger when it raises application failure. To inexperienced programmers, this behavior is more surprising than annoying. The only way to achieve this type of activity is to report the error via a non-update activity, such as a database pipe.

Disabled Triggers

Database triggers can be accidentally disabled or dropped by a person with sufficient privilege. This is actually nothing new. The problem is that there is no practical way of writing application code to guarantee that a trigger actually exists. If your application uses database triggers to audit sensitive data or accumulate summary tables, a disabled trigger can compromise the integrity of your whole system. The bad part is that nothing will be obviously wrong. The trigger is only performing a subtask; the originating action will still proceed. We design applications in which integrity is guaranteed by database triggers but cannot guarantee that the trigger actually exists or is enabled. DBAs must be aware of, and alert to, this potential problem.

No Version Control

Either database triggers exist or they don't; they are enabled or they are not. Other than building complex logic into the trigger text, we have no way to support multiple trigger versions on the one table.

Update OF COLUMN

The database trigger syntax includes an OF COLUMN extension that refines the sensitivity of the trigger. The trigger will execute only when that particular column (or columns) is updated. The problem with this approach is that the database thinks that setting a column back to its current value constitutes a change. The pros and cons of this approach can be debated long into the night, but what is annoying is the number of available application tools that insist on updating all table columns if any one of the columns has been modified. SQL*Forms is a prime example. All base table columns are updated back to the database, regardless of which column(s) were physically modified. For example:

Create or Replace Trigger Update_Sum_T1 
After Update OF salary
On EMP For Each Row
BEGIN
	IF NVL(:OLD.salary, 0) != NVL(:NEW.salary,0) THEN
		.
		.
	END IF;
END;

No Support of SYS Table Triggers

Oracle has decided that a database trigger cannot be associated with any table owner by user SYS. This is sound reasoning for all internal database tables (e.g., the V$ and X$ tables) but a little annoying for other non-critical SYS objects. For example, we've had situations in which trigger support for a a table such as SYS.AUD$ would have been handy. In this case, we wanted to check every user logging in to the database with a number of complex business rules (e.g., multiple connections, valid terminal IDs, client-server support for some database accounts and not for others). By enabling database auditing on the CONNECT function, we could have trapped the connect with an ON-INSERT TRIGGER on the SYS.AUD$ table, but it wasn't supported. (Note that moving the AUD$ table (with trigger) to another user account and creating a synonym in the SYS account is also not supported!)

Mutating Triggers

Database triggers are permitted to do almost anything except reference the table that instigated the trigger. This type of "loop" is known as a mutating trigger. Not being able to read or modify the record in question is understandable, and not being able to update any other record in the table is plausible, but not being able to read any other record in the table is a real nuisance. Earlier implementations of database trigger mutation could be circumnavigated via views, but this hole has now been plugged. The only legal way to achieve a mutating update now is to replicate all required data into a second mirror table and query the necessary information from that table via the trigger. The trigger would also need to be extended to maintain this mirrored table.

Hidden Behavior

The power of database triggers can also be its downfall. One of the most dangerous attributes of a database trigger is its hidden behavior. Updates can take far longer than they should or can generate strange, seemingly unrelated database errors. How many times have you thought, "I only inserted a single row into table xxx; it can't be my fault!" In such cases, you should immediately ask whether a trigger is involved. We've seen many developers desperately trying to tune their 20-line programs, absolutely devastated when the programs take 4 hours to run. Little do they know that for every row they insert, 100 rows may be getting inserted into seven different distributed databases, worldwide! They might not know that their organization had a Hong Kong office.