首页 > 代码库 > PL/SQL学习（六）触发器

PL/SQL学习（六）触发器

2024-08-12 10:17:31   218人阅读

原文参考：http://plsql-tutorial.com/

创建语法：
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- sql statements
END;
 
==创建说明：好长一段 不想翻译了、========================
  • CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
  • {BEFORE | AFTER | INSTEAD OF } - This clause indicates at what time should the trigger get fired. i.e for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view.
  • {INSERT [OR] | UPDATE [OR] | DELETE} - This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.
  • [OF col_name] - This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.
  • [ON table_name] - This clause identifies the name of the table or view to which the trigger is associated.
  • [REFERENCING OLD AS o NEW AS n] - This clause is used to reference the old and new values of the data being changed. By default, you reference the values as :old.column_name or :new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.
  • [FOR EACH ROW] - This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e.statement level Trigger).
  • WHEN (condition) - This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.
 
例子：
1) 创建 ‘product‘ 表和 ‘product_price_history‘ 表
CREATE TABLE product_price_history
(product_id number(5),
product_name varchar2(32),
supplier_name varchar2(32),
unit_price number(7,2) );
 
CREATE TABLE product
(product_id number(5),
product_name varchar2(32),
supplier_name varchar2(32),
unit_price number(7,2) );
2) 创建price_history_trigger触发器并执行
CREATE or REPLACE TRIGGER price_history_trigger
BEFORE UPDATE OF unit_price
ON product
FOR EACH ROW
BEGIN
INSERT INTO product_price_history
VALUES
(:old.product_id,
:old.product_name,
:old.supplier_name,
:old.unit_price);
END;
/
3) 修改 product 的记录
UPDATE PRODUCT SET unit_price = 800 WHERE product_id = 100
4)修改时在提交前执行回滚操作，触发器中的插入操作同样会回滚
 
类型：
1) 行级别触发器
2) 语句级别触发器
 
触发器操作：
DESC USER_TRIGGERS;
SELECT * FROM user_triggers WHERE trigger_name = ‘trigger_name‘;
DROP TRIGGER trigger_name;
 
触发器死循环：
1) The INSERT Trigger, triggerA on table ‘abc‘ issues an UPDATE on table ‘xyz‘.
2) The UPDATE Trigger, triggerB on table ‘xyz‘ issues an INSERT on table ‘abc‘.

PL/SQL学习（六）触发器


联系
我们