Trigger的缺点

原文地址：http://www.cine.idv.tw/learning/computer-science/database/oracle/trigger-76847f3a9ede

shortie 對使用資料庫端提供的程式機制，一直採取比較積極的態度，也就是說面對一個應用程式的問題，如果可以用資料庫端的 stored procedure/function, trigger 來解決， shortie 一定會先使用資料庫的解決方案。但是凡事都是好壞並存，偶而關注一下資料庫程式物件的缺點也有益處。

(本文取自 Knowledge Xpert for PL/SQL)

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.

作者提到可以用 trigger 的數量, 大小, 類型來評斷設計的品質。

If there are fewer than 10 database triggers, this usually indicates that an older application has been migrated from an Oracle6 environment.

trigger 不到 10 個，通常顯示這是一個從舊的 Oracle 翻新過來的應用程式，或者是設計人員缺乏應用 trigger 的能力。

Fifty or more database triggers indicate functionality indecision by the users during the build phase and probable performance issues.

trigger 超過 50 個以上，則顯示出未確實掌握使用者需求，也有可能會造成執行效能不佳的結果。

A number between 10 and 50 leads us to believe that the application was carefully designed and developed for Oracle7 or above deployment.

trigger 在 10 到 50 個中間，則顯示出這是一個細心設計的結果，且充分發揮了 Oracle 7 (或更新版本) 的能力。

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.

基本上，現在 shortie 碰到的版本都支援事先編譯好的 trigger ，因此這個缺點基本上可以忽略。

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.

shortie 從來就不會想要去寫一個 SELECT trigger ，相信你也沒想過才對。

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.

只要知道 AUTONOMOUS_TRANSACTION 這個東西，應該不會有這個困擾。

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.

這倒是個問題， shortie 常常碰到因為 trigger 失效，導致應用程式出現莫名奇妙的錯誤，或是資料庫中的資料看起來有點詭異。

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.
這個只要維持良好的開發習慣，將命令稿交給 SCM 來控管，應該不是問題。

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;

shortie 也常看到這種例子，在這種狀況下 trigger 裡面的判斷邏輯要自己控管好。

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!)
因為 shortie 的權限等級太低，還操不到心。

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.
這個碰到的機率很高，(例如 shortie 常常看到 insert trigger 要去檢查自己這個 table 是否已經有資料，如果有就 insert 失敗，否則就 insert 成功) 通常顯示出設計上面的問題。碰到這個狀況，要記得 Oracle 的 trigger 有 before/after 之分。

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.

這個問題恐怕是 shortie 最困擾的，缺乏文件 (或是閱讀性很差的文件) ，常常讓應用程式的原始開發人員成為阻礙後續維護發展的阻力，因為這些 hidden behavior 只有這些人清楚，外人很難介入。