数据库 Trigger

什么是触发器(Trigger)?

触发器是一种特殊类型的存储过程,它不同于存储过程。触发器主要是通过事件进行触发而被执行的,触发器的触发事件分可为3类,分别是DML事件、DDL事件和数据库事件,而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如UPDATE、 INSERT、 DELETE 这些操作时,SQL Server 就会自动执行触发器所定义的SQL 语句,从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。

  触发器是特定事件出现的时候,自动执行的代码块。类似于存储过程,但是用户不能直接调用他们。

  触发器功能

  1、 允许/限制对表的修改

  2、 自动生成派生列,比如自增字段

  3、 强制数据一致性

  4、 提供审计和日志记录

  5、 防止无效的事务处理

  6、 启用复杂的业务逻辑

  触发器种类

  触发器的种类可划分为4种:1.数据操纵语言(DML)触发器、2.替代(INSTEAD OF)触发器、3.数据定义语言(DDL)触发器、4.数据库事件触发器。

一个关于Oracle数据库触发器的语法介绍:


Create trigger in Oracle

  
create trigger trigger-name before event
create trigger trigger-name after event
create trigger trigger-name instead of event

Event

The code associated with a trigger is fired when a specified event occurs. The events can either be a  DML event, a  DDL event or a  database event. (DDL event and database event triggers are also called  system triggers)

DML event

delete on table-name
insert on table-name
update on table-name
update of column-name on table-name
update of column-name-1, column-name-2 on ...

delete or insert on table-name
delete or update on table-name
....

DDL event

ddl-event on schema
ddl-event or ddl-event on schema
ddl-event or ddl-event or ddl-event ... on schema

ddl-event on database schema
ddl-event or ddl-event on database
ddl-event or ddl-event or ddl-event ... on database

Database event

database-event on schema
database-event or database-event on schema
database-event or database-event or database-event on schema ...

database-event on database
database-event or database-event on database
database-event or database-event or database-event on database ...
The following database events can be caught:

Prerequisites

dbmsstdx.sql must have been called. Normally,  catalog.sql is run after the creation of a database. Catalog.sql in turn calls dbmsstdx.sql.

before insert or update

create or replace trigger <TRIGGER_NAME>
  before insert or update
on <table_name>
  for each row
declare
  <VARIABLE DECLARATIONS>
begin
    <CODE>
exception
    <EXCEPTION HANDLERS>
end <TRIGGER_NAME>;
/

After Logon on database

create table logon_tbl (who varchar2(30), when date);

create or replace trigger trg_logon_db
  after logon on database
begin
  insert into logon_tbl (who, when) values (user, sysdate);
end;
/

Trigger related functions

So called  event attribute functions can be used within a trigger.

Disabling system triggers

System triggers can be disabled by setting _system_trig_enabled to false.
触发器例子

更新t_a的code字段内容时,能触发t_b的code字段内容同步更新。
CREATE OR REPLACE TRIGGER   update_a
AFTER UPDATE
ON  T_A 
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   
    UPDATE  T_B     SET code=:NEW.CODE WHERE T_B.CODE=:OLD.CODE;
 
EXCEPTION
     WHEN OTHERS THEN
              
       RAISE;
END inset_a;

其他语法:

Referencing 子句: 执行DML语句之前的值的默认名称是 :old ,之后的值是 :new insert 操作只有:new delete 操作只有 :old update 操作两者都有

referencing子句只是将new 和old重命名为new_value和old_value,目的是避免混淆。比如操作一个名为new的表时。

其他资料:
删除SQL语句:
 
ALTER TRIGGER 名称 DISABLE;
drop trigger 名称;
常见错误:
ORA-04091: table is mutating, trigger/function may not see it

关于这个错误,其实是由于对本表的操作造成的.ORACLE DB里默认在写TRIGGER的时候把本表锁死,不允许对其进行操作,也就是说这个错误是不能通过系统的手段解决的,只能改用一些其它的SQL来绕开它.
刚接触TRIGGER的时候会经常犯这样的错误,其中有大部分是可以通过:new和ld来解决的,这是新手在写trigger时最需要注意的问题之一.
但是还会有一定要对本表进行修改或查询操作的情况,不能避免.

解决办法:

 CREATE OR REPLACE TRIGGER TR_T
  2  AFTER DELETE ON T
  3  FOR EACH ROW
  4  DECLARE V_COUNT NUMBER;
  5  PRAGMA AUTONOMOUS_TRANSACTION;
  6  BEGIN
  7     INSERT INTO T VALUES(:OLD.ID,:OLD.MC,SYSDATE);
  8     COMMIT;
  9  END TR_DEL_CABLE;

AUTONOMOUS_TRANSACTION是指在function,procedure,trigger等subprograms中对事务进行自治管理,当在别的pl/sql block里取调用这些subprograms的时候这些subprograms并不随着父pl/sql block的失败而回滚,而是自己管自己commit;

王海良@Chatopera 聊天机器人 机器学习 智能客服
Chatopera 联合创始人 & CEO,运营聊天机器人平台 https://bot.chatopera.com,让聊天机器人上线!2015年开始探索聊天机器人的商业应用,实现基于自然语言交互的流程引擎、语音识别、自然语言理解,2018年出版《智能问答与深度学习》一书。