oracle 触发器
一、触发器简介
触发器的定义就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。
因此触发器不需要人为的去调用，也不能调用。
然后，触发器的触发条件其实在你定义的时候就已经设定好了。
这里面需要说明一下，触发器可以分为语句级触发器和行级触发器。
详细的介绍可以参考网上的资料，简单的说就是语句级的触发器可以在某些语句执行前或执行后被触发。而行级触发器则是在定义的了触发的表中的行数据改变时就会被触发一次。
具体举例：
1、 在一个表中定义的语句级的触发器，当这个表被删除时，程序就会自动执行触发器里面定义的操作过程。这个就是删除表的操作就是触发器执行的条件了。
2、 在一个表中定义了行级的触发器，那当这个表中一行数据发生变化的时候，比如删除了一行记录，那触发器也会被自动执行了。
二、触发器语法
触发器的语法：
create [or replace] tigger 触发器名 触发时间 触发事件 on 表名 [for each row] begin pl/sql语句 end
其中：
触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。
触发时间：指明触发器何时执行，该值可取：
before：表示在数据库动作之前触发器执行;
after：表示在数据库动作之后触发器执行。
触发事件：指明哪些数据库动作会触发此触发器：
insert：数据库插入会触发此触发器;
update：数据库修改会触发此触发器;
delete：数据库删除会触发此触发器。
表 名：数据库触发器所在的表。
for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。
触发器能实现如下功能：
功能：
0、 数据字典
1、 允许/限制对表的修改
2、 自动生成派生列，比如自增字段
3、 对表进行细粒度监控、返回操作此表详细信息
4、 提供审计和日志记录
5、 防止无效的事务处理
6、 利用视图插入数据到落地表
0)、数据字典
--相关数据字典 SELECT * FROM USER_TRIGGERS; --必须以DBA身份登陆才能使用此数据字典 SELECT * FROM ALL_TRIGGERS; SELECT * FROM DBA_TRIGGERS; --启用和禁用 ALTER TRIGGER trigger_name DISABLE; ALTER TRIGGER trigger_name ENABLE;
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
5)、对薪资进行触发操作
--创建触发器 set serveroutput on;
CREATE OR REPLACE TRIGGER SAL_EMP BEFORE UPDATE ON EMP FOR EACH ROW BEGIN IF :OLD.SAL > :NEW.SAL THEN DBMS_OUTPUT.PUT_LINE('工资减少'); ELSIF :OLD.SAL < :NEW.SAL THEN DBMS_OUTPUT.PUT_LINE('工资增加'); ELSE DBMS_OUTPUT.PUT_LINE('工资未作任何变动'); END IF; DBMS_OUTPUT.PUT_LINE('更新前工资 ：' || :OLD.SAL); DBMS_OUTPUT.PUT_LINE('更新后工资 ：' || :NEW.SAL); END; / --执行UPDATE查看效果 UPDATE emp SET sal = 3000 WHERE empno = '7788';
6)、利用视图插入数据到表
--创建表 CREATE TABLE tab1 (tid NUMBER(4) PRIMARY KEY,tname VARCHAR2(20),tage NUMBER(2)); CREATE TABLE tab2 (tid NUMBER(4),ttel VARCHAR2(15),tadr VARCHAR2(30));
--插入数据 INSERT INTO tab1 VALUES(101,'zhao',22); INSERT INTO tab1 VALUES(102,'yang',20); INSERT INTO tab2 VALUES(101,'13761512841','AnHuiSuZhou'); INSERT INTO tab2 VALUES(102,'13563258514','AnHuiSuZhou');
--创建视图连接两张表 CREATE OR REPLACE VIEW tab_view AS SELECT tab1.tid,tname,ttel,tadr FROM tab1,tab2 WHERE tab1.tid = tab2.tid;
--创建触发器 CREATE OR REPLACE TRIGGER TAB_TRIGGER INSTEAD OF INSERT ON TAB_VIEW --INSTEAD OF 触发器只作用于视图上，不能作用于表上。 --本来是不能在视图上INSERT、UPDATE、DELETE数据的，创建INSTEAD OF 触发器后，就可以了。 BEGIN INSERT INTO TAB1 (TID, TNAME) VALUES (:NEW.TID, :NEW.TNAME); INSERT INTO TAB2 (TTEL, TADR) VALUES (:NEW.TTEL, :NEW.TADR); END; /
--现在就可以利用视图插入数据 INSERT INTO tab_view VALUES(106,'ljq','13886681288','beijing');
--查询 SELECT * FROM tab_view; SELECT * FROM tab1; SELECT * FROM tab2;
7)、创建触发器，将操作CREATE、DROP存储在log_info表
--创建表 CREATE TABLE log_info( manager_user VARCHAR2(15), manager_date VARCHAR2(15), manager_type VARCHAR2(15), obj_name VARCHAR2(15), obj_type VARCHAR2(15) ); --创建触发器 set serveroutput on; CREATE OR REPLACE TRIGGER TRIG_LOG_INFO AFTER CREATE OR DROP ON SCHEMA BEGIN INSERT INTO LOG_INFO VALUES (USER, SYSDATE, SYS.DICTIONARY_OBJ_NAME, SYS.DICTIONARY_OBJ_OWNER, SYS.DICTIONARY_OBJ_TYPE); END; / --测试语句 CREATE TABLE a(id NUMBER); CREATE TYPE aa AS OBJECT(id NUMBER); DROP TABLE a; DROP TYPE aa; --查看效果 SELECT * FROM log_info;
