oracle 触发器

King

一、触发器简介

触发器的定义就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。

因此触发器不需要人为的去调用，也不能调用。

然后，触发器的触发条件其实在你定义的时候就已经设定好了。

这里面需要说明一下，触发器可以分为语句级触发器和行级触发器。

详细的介绍可以参考网上的资料，简单的说就是语句级的触发器可以在某些语句执行前或执行后被触发。而行级触发器则是在定义的了触发的表中的行数据改变时就会被触发一次。

具体举例：

1、在一个表中定义的语句级的触发器，当这个表被删除时，程序就会自动执行触发器里面定义的操作过程。这个就是删除表的操作就是触发器执行的条件了。
2、在一个表中定义了行级的触发器，那当这个表中一行数据发生变化的时候，比如删除了一行记录，那触发器也会被自动执行了。

二、触发器语法

触发器的语法：

create [or replace] tigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
 pl/sql语句
end

其中：

触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。
触发时间：指明触发器何时执行，该值可取：
before：表示在数据库动作之前触发器执行;
after：表示在数据库动作之后触发器执行。
触发事件：指明哪些数据库动作会触发此触发器：
insert：数据库插入会触发此触发器;
update：数据库修改会触发此触发器;
delete：数据库删除会触发此触发器。
表名：数据库触发器所在的表。
for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。

触发器能实现如下功能：

功能：

1、允许/限制对表的修改
2、自动生成派生列，比如自增字段
3、对表进行细粒度监控、返回操作此表详细信息
4、提供审计和日志记录
5、防止无效的事务处理
6、启用复杂的业务逻辑

举例：

1)、下面的触发器在更新表tb_emp之前触发，目的是不允许在周二修改表：

1.1.创建触发器

create or replace trigger tri_auth_secure before insert or update or delete
on t_ora_emp
begin
  if(to_char(sysdate,'DY')='TUE') then
    RAISE_APPLICATION_ERROR(-20600,'不能在周二修改表t_ora_emp');
  end if;
end;
/

1.2.删除数据

SQL> delete t_ora_emp t where t.empno = 7788;
delete t_ora_emp t where t.empno = 7788
       *
ERROR at line 1:
ORA-20600: 不能在周二修改表t_ora_emp

2)、使用触发器实现序号自增

2.1.创建测试表：

create table t_tab_user(
  id number(11) primary key,
  username varchar(50),
  password varchar(50)
);

2.2.创建测试序列：

create sequence my_seq increment by 1 start with 1 nomaxvalue nocycle cache 20;

2.3.创建触发器：

CREATE OR REPLACE TRIGGER MY_TGR
 BEFORE INSERT ON T_TAB_USER
 FOR EACH ROW--对表的每一行触发器执行一次
DECLARE
 NEXT_ID NUMBER;
BEGIN
 SELECT MY_SEQ.NEXTVAL INTO NEXT_ID FROM DUAL;
 :NEW.ID := NEXT_ID; --:NEW表示新插入的那条记录
END;

2.4.插入测试数据：

insert into t_tab_user(username,password) values('admin','admin');
insert into t_tab_user(username,password) values('fgz','fgz');
insert into t_tab_user(username,password) values('test','test');
commit;

2.5.查询结果：

SQL> col username for a30
SQL> col password for a30
SQL> select * from t_tab_user;

        ID USERNAME                       PASSWORD
---------- ------------------------------ ------------------------------
         1 admin                          admin
         2 fgz                            fgz
         3 test                           test

3)、对表进行监控

3.1.创建测试表

-- Create table
create table T_TRIG_SQL
(
  sys_date  DATE not null,
  sid       NUMBER,
  serial#   NUMBER,
  username  VARCHAR2(30),
  osuser    VARCHAR2(64),
  machine   VARCHAR2(32),
  terminal  VARCHAR2(16),
  program   VARCHAR2(64),
  sqltext   VARCHAR2(2000),
  status    VARCHAR2(30),
  client_ip VARCHAR2(60)
);
-- Add comments to the columns 
comment on column T_TRIG_SQL.sys_date
  is '操作时间';
comment on column T_TRIG_SQL.sid
  is '会话唯一标识';
comment on column T_TRIG_SQL.serial#
  is '唯一序列号';
comment on column T_TRIG_SQL.username
  is '数据库用户';
comment on column T_TRIG_SQL.osuser
  is '客户端操作系统用户名';
comment on column T_TRIG_SQL.machine
  is '客户端全名';
comment on column T_TRIG_SQL.terminal
  is '客户端名';
comment on column T_TRIG_SQL.program
  is '客户端应用程序';
comment on column T_TRIG_SQL.sqltext
  is 'SQL文本';
comment on column T_TRIG_SQL.status
  is '增删改';
comment on column T_TRIG_SQL.client_ip
  is 'IP地址 ';

3.2.创建触发器

create or replace trigger tri_of_table
  after insert or update or delete on t_objects for each row
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  IF inserting THEN
    INSERT INTO trig_sql
        select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
               s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
               'INSERT',
              sys_context('userenv','ip_address')
          from v$sql q, v$session s
         where s.audsid=(select userenv('SESSIONID') from dual)
           and s.prev_sql_addr=q.address
           AND s.PREV_HASH_VALUE = q.hash_value;
    COMMIT;
  ELSIF deleting  then
      INSERT INTO trig_sql
           select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
                       s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
                       'DELETE',
                       sys_context('userenv','ip_address')
             from v$sql q, v$session s
            where s.audsid=(select userenv('SESSIONID') from dual)
             and s.prev_sql_addr=q.address
             AND s.PREV_HASH_VALUE = q.hash_value;
    COMMIT;
  ELSIF updating then
    INSERT INTO trig_sql
         select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
                     s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
                     'UPDATE',
                     sys_context('userenv','ip_address')
           from v$sql q, v$session s
          where s.audsid=(select userenv('SESSIONID') from dual)
           and s.prev_sql_addr=q.address
           AND s.PREV_HASH_VALUE = q.hash_value;
    COMMIT;
   END IF;
END;

3.3.对表进行操作

update t_objects t
   set t.created = sysdate
 where t.owner = 'OS_INSIGHT'
   and rownum <= 1;

3.4.查看执行操作记录

select * from T_TRIG_SQL ;

4)、创建触发器，用来记录表的删除数据

4.1.创建测试表

create table t_tab_emp as select * from emp;

create table t_old_emp as select * from t_tab_emp where 1=2;

4.2.创建触发器

create or replace trigger tri_delete_trace 
after delete on t_tab_emp
for each row  --语句级触发，即每一行触发一次
  declare
  begin
    insert into t_old_emp
    values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);--old 代表旧值
    --commit;  ora-04092:cannot commit in a trigger 
    --不能使用事务控制语句（ROLLBACK, COMMIT,SAVEPOINT）。
    --特别注意的是，在触发器的主体中引用的函数（function）/过程（procedure）中也不能有事物控制语句。
    end;

4.3.查询已删除数据

SQL> col ename for a10
SQL> col job for a10
SQL> select * from t_old_emp;

EMPNO ENAME      JOB          MGR HIREDATE             SAL       COMM DEPTNO
----- ---------- ---------- ----- ------------------ ----- ---------- ------
 7788 SCOTT      ANALYST     7566 19-APR-87           3000                20

转：https://www.cnblogs.com/bhlsheji/p/4050093.html

计算机

2022年2月02日