FirebirdSQL/3/Trigger

出自VFP Wiki

跳轉到: 導航, 搜尋

目錄

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.

相關文章