FirebirdSQL/3/Trigger
出自VFP Wiki
（修訂版本間差異）
在2005年1月20日 (四) 15:09所做的修訂版本
目錄 |
What is Triggers
觸發（Triggers）是當Table或是View因為Insert、Delete、Update動作發生時自動觸發的程序。可以用來描述商業邏輯。跟預存程序很類似，差別在於它不是用來直接呼叫的。
TRIGGER 語法
- TRIGGER 建立的語法:
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;}
|| 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 修改的語法:
[ACTIVE | INACTIVE]
[{BEFORE | AFTER} {DELETE | INSERT | UPDATE}]
[POSITION number]
AS < trigger_body>;
- TRIGGER 移除的語法:
DROP TRIGGER name;
TRIGGER 範例
- 建立TRIGGER
解釋：因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 ; !!
- 修改TRIGGER
ALTER TRIGGER SAVE_SALARY_CHANGE INACTIVE;
- 移除TRIGGER
DROP TRIGGER SET_CUST_NO;
- NEW. column 與 OLD. column
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.
