PL/SQL学习（六）触发器
时间：2021-07-01 10:21:17
帮助过：2人阅读
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学习（六）触发器
标签：
