FirebirdSQL/3/Trigger

=== What is Triggers ===
觸發（Triggers）是當Table或是View因為Insert、Delete、Update動作發生時自動觸發的程序。可以用來描述商業邏輯。跟預存程序很類似，差別在於它不是用來直接呼叫的。

=== TRIGGER 語法 ===
*TRIGGER 建立的語法:
<pre>
CREATE TRIGGER name FOR { table | view}
[ACTIVE | INACTIVE]
{BEFORE | AFTER} {DELETE | INSERT | UPDATE}
[POSITION number]
AS < trigger_body>
<trigger_body> = [<variable_declaration_list>] < block>
<variable_declaration_list> =DECLARE VARIABLE variable datatype;
[DECLARE VARIABLE variable datatype; …]
<block> =
BEGIN
<compound_statement> [<compound_statement> …]
END
<compound_statement> = {<block> | statement;}
</pre>

||  Argument          ||    語法說明  ||        
|| name　|| trigger的名稱.在資料庫中是唯一的. || 
|| table || 會產生trigger動作的Table或View名稱|| 
|| ACTIVE|INACTIVE || ACTIVE: 預設值.會產生動作. INACTIVE: 忽略/跳過此trigger程序.||
|| BEFORE|AFTER || BEFORE: 在DELETE, INSERT, or UPDATE.之前會執行的程序. AFTER: 在DELETE, INSERT, or UPDATE.之後會執行的程序.||
|| DELETE|INSERT | UPDATE || 表示trigger程序會因為DELETE, INSERT, or UPDATE而執行||
|| POSITION number || 執行的等級.預設等級為0 ||
|| DECLARE VARIABLE var<datatype> || 宣告變數 ||
|| statement || 主要的程序.由BEGIN and END 所組成的 ||
|| terminator || ||

*TRIGGER 修改的語法:
<pre>
[ACTIVE | INACTIVE]
[{BEFORE | AFTER} {DELETE | INSERT | UPDATE}]
[POSITION number]
AS < trigger_body>;
</pre>
*TRIGGER 移除的語法:
<pre>
DROP TRIGGER name;
</pre>

=== TRIGGER 範例 ===
*建立TRIGGER
<pre>
解釋：因Update 員工(EMPLOYEE)而產生TRIGGER SAVE_SALARY_CHANGE事件  
如果薪資被異動(old.salary <> new.salary)
就記錄被異動前的員工編號(old.emp_no),使用者(USER),舊薪資old.salary
新舊薪資的差異比(new.salary - old.salary) * 100 / old.salary)

SET TERM !! ;
CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE
AFTER UPDATE AS
BEGIN
IF (old.salary <> new.salary) THEN
INSERT INTO SALARY_HISTORY (EMP_NO, CHANGE_DATE,
UPDATER_ID, OLD_SALARY, PERCENT_CHANGE)
VALUES (old.emp_no, 'now', USER, old.salary,
(new.salary - old.salary) * 100 / old.salary);
END !!
SET TERM ; !!
</pre>

*修改TRIGGER
<pre>
ALTER TRIGGER SAVE_SALARY_CHANGE INACTIVE;
</pre>

*移除TRIGGER
<pre>
DROP TRIGGER SET_CUST_NO;
</pre>
*NEW. column 與 OLD. column
<pre>
INSERT and tries to assign a value to NEW.column will have no effect. The actual column values
are not altered until after the action, so triggers that reference values from their target
tables will not see a newly inserted or updated value unless they fire after UPDATE or
INSERT.
</pre>

=== 相關文章 ===
*[http://www.geocities.com/jzjhmud/essay/essay4.htm 如何用 TRIGGERS (by 誰與爭鋒)]
*[http://netlab.cse.yzu.edu.tw/~statue/cfc/docs/pgsqldoc-7.0c/triggers.htm PostgreSQL觸發器]
*[http://vfp.sunyear.com.tw/viewtopic.php?p=4766#4766 從SQL Server轉換到FirebirdSQL] 
*[http://community.borland.com/article/interbase/dbase.pdf Upsizing From dBase to Interbase]
*[http://www.xlprueba.com.ar/ib/defaulthtml/ch01.html Converting from Microsoft SQL Server to Firebird]
*[http://www.csdn.net/Subject/251/ 資料庫設計規範]
*[http://www.hkln.net/doc/oracle/sql/index.htm Oracle SQL]
*[http://fjt.todayisp.com:7751/www.100down.com/download/infoList/Catalog_24_1.html 百當SQL]
*[http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_jim_triggers Generic Triggers For InterBase?]