触发器
触发器触发顺序:
---------------------------------------------------------------------------------
before statement trigger(on table)->before row trigger (on table for each row)->
after row trigger (on table for each row)->after statement trigger (on table)
create or replace trigger e_update1
before update on e
begin
if updating then
dbms_output.put_line('table before');
end if;
end;
/
create or replace trigger e_update2
before update on e for each row
begin
if updating then
dbms_output.put_line('row before');
end if;
end;
/
create or replace trigger e_update3
after update on e for each row
begin
if updating then
dbms_output.put_line('row after');
end if;
end;
/
create or replace trigger e_update4
after update on e
begin
if updating then
dbms_output.put_line('table after');
end if;
end;
/
update e set sal=sal+1 where empno=7369;
--验证触发器的状态
select trigger_name,status from user_triggers;
--禁用某个触发器
ALTER TRIGGER e_update3 disable;
--禁用某个表上的所有触发器
alter table e disable all triggers;
--删除触发器
DROP TRIGGER e_update3;
练习 1:DML触发器
---------------------------------------------------------------------------------
表级触发器
行级触发器
create or replace trigger e_update
before update of sal on e
for each row
begin
if updating then
raise_application_error(-20001,'salary do not modify!');
end if;
end;
/
练习 2:DML触发器限制数据修改
---------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER secure_emp
before INSERT ON e
BEGIN
IF TO_CHAR (SYSDATE, 'HH24') NOT BETWEEN '08' AND '09'
OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR (-20205, 'insert into table E！');
END IF;
END;
/
练习 3:更新d表deptno时自动更新e表deptno
---------------------------------------------------------------------------------
CREATE or replace TRIGGER vdate
AFTER UPDATE OF deptno ON d FOR EACH ROW
BEGIN
IF (UPDATING AND :old.deptno != :new.deptno) THEN --关联标识（保留结构）
UPDATE e
SET deptno = :new.deptno
WHERE deptno = :old.deptno;
END IF;
END;
/
练习 4:删除d表数据时自动删除e表数据
---------------------------------------------------------------------------------
CREATE or replace TRIGGER vdate
AFTER DELETE ON d FOR EACH ROW
BEGIN
IF DELETING THEN
delete e
WHERE deptno = :old.deptno;
END IF;
END;
/
练习 5:删除d表数据时将e表deptno置空
---------------------------------------------------------------------------------
CREATE or replace TRIGGER vdate
AFTER DELETE ON d FOR EACH ROW
BEGIN
IF DELETING THEN
UPDATE e
SET deptno = :new.deptno
WHERE deptno = :old.deptno;
END IF;
END;
/
练习 6:删除d表数据时将e表deptno置空，更新d表deptno时自动更新e表deptno
---------------------------------------------------------------------------------
CREATE or replace TRIGGER vdate
AFTER delete or UPDATE OF deptno ON d FOR EACH ROW
BEGIN
IF deleting or (UPDATING AND :old.deptno != :new.deptno) THEN
UPDATE e
SET deptno = :new.deptno
WHERE deptno = :old.deptno;
END IF;
END;
/
练习 7:删除d表数据时自动删除e表数据，更新d表deptno时自动更新e表deptno
---------------------------------------------------------------------------------
CREATE or replace TRIGGER vdate
AFTER delete or UPDATE OF deptno ON d FOR EACH ROW
BEGIN
IF (UPDATING AND :old.deptno != :new.deptno) THEN
UPDATE e
SET deptno = :new.deptno
WHERE deptno = :old.deptno;
elsif deleting then
delete e
WHERE deptno = :old.deptno;
END IF;
END;
/
练习 8:trigger不能含有事物处理的语句!需要使用自制事务！
---------------------------------------------------------------------------------
create or replace procedure Autonomous
(v_sal e.sal%type,v_empno e.empno%type)
is
PRAGMA Autonomous_transaction; --声明为自治事物
begin
update e set sal=v_sal where empno=v_empno;
commit;
end Autonomous;
/
--如果emp表的sal被更新那么自动更新e表的sal
create or replace trigger cascade_update_e
before update of sal on emp for each row
declare
v_sal e.sal%type;
v_empno e.empno%type;
begin
v_sal := :new.sal;
v_empno := :new.empno;
Autonomous(v_sal,v_empno);
end;
/
练习 9:系统触发器
---------------------------------------------------------------------------------
conn scott/tiger
drop table log_table;
create table log_table(sid number,
serial# number,
username varchar2(30),
action varchar2(8),
log_time varchar2(19));
grant select on scott.log_table to public;
grant insert on scott.log_table to public;
--用户级别：
create or replace trigger scott_logon
after logon on schema
declare
v_sid v$mystat.sid%type;
v_serial# v$session.serial#%type;
v_username v$session.username%type;
begin
select sid into v_sid from v$mystat where rownum=1;
select serial#,username
into v_serial#,v_username
from v$session
where sid=v_sid;
insert into scott.log_table
values (v_sid,v_serial#,v_username,'logon',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end scott_logon;
/
create or replace trigger scott_logof
before logoff on schema
declare
v_sid v$mystat.sid%type;
v_serial# v$session.serial#%type;
v_username v$session.username%type;
v_status v$session.status%type;
begin
select sid into v_sid from v$mystat where rownum=1;
select serial#,username
into v_serial#,v_username
from v$session
where sid=v_sid;
insert into scott.log_table
values (v_sid,v_serial#,v_username,'logoff',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end scott_logof;
/
--通过用户级别触发器修改排序方法:（也可以修改语言选项或日期格式）
CONN SCOTT/TIGER
CREATE OR REPLACE TRIGGER test_tri
AFTER LOGON ON schema
DECLARE
sqlstr VARCHAR2(200) := 'alter session set nls_sort = SCHINESE_STROKE_M';
BEGIN
execute immediate sqlstr;
END test_tri;
/
--通过数据库级别触发器修改排序方法:
CONN / AS SYSDBA
CREATE OR REPLACE TRIGGER test_tri
AFTER LOGON ON DATABASE
DECLARE
sqlstr VARCHAR2(200) := 'alter session set nls_sort = SCHINESE_STROKE_M';
BEGIN
IF (USER = 'SCOTT') THEN
execute immediate sqlstr;
END IF;
END test_tri;
/
--数据库级别：得具有访问v$的权限!
conn sys/oracle as sysdba
create or replace trigger LogSchemaConnects
after logon on database
declare
v_sid v$mystat.sid%type;
v_serial# v$session.serial#%type;
v_username v$session.username%type;
begin
select sid into v_sid from v$mystat where rownum=1;
select serial#,username
into v_serial#,v_username
from v$session
where sid=v_sid;
insert into scott.log_table
values (v_sid,v_serial#,v_username,'logon',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end LogSchemaConnects;
/
create or replace trigger dbshutdown
before shutdown on database
declare
v_sid v$mystat.sid%type;
v_serial# v$session.serial#%type;
v_username v$session.username%type;
begin
select sid into v_sid from v$mystat where rownum=1;
select serial#,username
into v_serial#,v_username
from v$session
where sid=v_sid;
insert into scott.log_table
values (v_sid,v_serial#,v_username,'ShutDown',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end dbshutdown;
/
--限制指定用户从指定IP登录:
CREATE OR REPLACE TRIGGER TRIGGER_RESTRICT_LOGON
AFTER LOGON ON DATABASE
DECLARE
RESTRICTED_USER VARCHAR2(32) := 'SCOTT';
ALLOWED_IP VARCHAR2(16) := '10.1.1.244';
LOGON_USER VARCHAR2(32);
CLIENT_IP VARCHAR2(16);
BEGIN
LOGON_USER := SYS_CONTEXT('USERENV','SESSION_USER');
CLIENT_IP := NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'), 'NULL');
IF LOGON_USER = RESTRICTED_USER AND CLIENT_IP <> ALLOWED_IP THEN
RAISE_APPLICATION_ERROR(-20001, RESTRICTED_USER || ' is not allowed to connect from ' || CLIENT_IP);
END IF;
END;
/
练习 10:替代触发器(由视图的dml操作所触发)
-------------------------------------------------------------------------------------------
CREATE OR REPLACE VIEW VVV AS
SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO;
INSERT INTO VVV VALUES (7777,'zjz',50,'SALES','Beijing');
CREATE OR REPLACE TRIGGER insert_EMP_AND_DEPT
INSTEAD OF INSERT ON VVV
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
IF INSERTING THEN
INSERT INTO DEPT (DEPTNO, DNAME, LOC)
VALUES (
:new.DEPTNO,
:new.DNAME,
:new.LOC);
INSERT INTO EMP
(EMPNO, ENAME, DEPTNO)
VALUES (
:new.EMPNO,
:new.ENAME,
:new.DEPTNO);
ELSIF DELETING THEN
NULL;
ELSE
NULL;
END IF;
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (
num=> -20107,
msg=> 'Duplicate EMP or DEPT ID');
END insert_EMP_AND_DEPT
/
