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.

相關文章

• 如何用 TRIGGERS (by 誰與爭鋒)
• PostgreSQL觸發器
• 從SQL Server轉換到FirebirdSQL
• Upsizing From dBase to Interbase
• Converting from Microsoft SQL Server to Firebird
• 資料庫設計規範
• Oracle SQL
• 百當SQL
• Generic Triggers For InterBase?

数据恢复 硬盘数据恢复 磁带数据恢复 raid数据恢复 磁盘阵列数据恢复 数据恢复 数据恢复 数据恢复 数据修复 硬盘数据修复 磁带数据修复 raid数据修复 数据修复 数据修复 数据修复 磁盘阵列数据修复