Oracle 触发器

分类： Linux

begin

if inserting then

:new.job:=upper(:new.job);

else

:new.job:=upper(:new.job);

end if;

end;

2.3、instead of 触发器.

(此触发器是在视图上而不是在表上定义的触发器,它是用来替换所使用实际语句的触发器.)

语法如下:

create or replace trigger trig_test

instead of insert or update on 表名

referencing new as n

for each row

declare

..........

begin

........

end;

2.4、模式触发器.

可以在模式级的操作上建立触发器.

实例如下:

create or replace trigger log_drop_obj

after drop on schema

begin

insert into .....

end;

2.5、数据库级触发器.

可以创建在数据库事件上的触发器,包括关闭,启动,服务器错误,登录等.这些事件都是实例范围的,不与特定的表或视图关联.

实例:

create or replace trigger trig_name

after startup on database

begin

...........

end;

OK,大概就这些了.

2.6、例子：

需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的特权。

Create table foo(a number);

Create trigger biud_foo

       Before insert or update or delete

       On foo

Begin

    If user not in (‘DONNY’) then

       Raise_application_error(-20001, ‘You don’t have access to modify this table.’);

    End if;

End;

/

即使SYS，SYSTEM用户也不能修改foo表

2.7、[试验]

对修改表的时间、人物进行日志记录。

1、 建立试验表

create table employees_copy as select *from hr.employees

2、 建立日志表

create table employees_log(

        who varchar2(30),

        when date);

3、 在employees_copy表上建立语句触发器，在触发器中填充employees_log 表。

Create or replace trigger biud_employee_copy

       Before insert or update or delete

       On employees_copy

       Begin

           Insert into employees_log(Who,when)

           Values( user, sysdate);

       End;

       /

4、 测试

update employees_copy set salary= salary*1.1;

select *from employess_log;

5、 确定是哪个语句起作用？

即是INSERT/UPDATE/DELETE中的哪一个触发了触发器？

可以在触发器中使用INSERTING / UPDATING / DELETING 条件谓词，作判断：

begin

        if inserting then

               -----

        elsif updating then

               -----

        elsif deleting then

               ------

        end if;

end;

if updating(‘COL1’) or updating(‘COL2’) then

        ------

end if;

2.8、[试验]

1、 修改日志表

alter table employees_log

add (action varchar2(20));

2、 修改触发器，以便记录语句类型。

Create or replace trigger biud_employee_copy

Before insert or update or delete

On employees_copy

Declare

L_action employees_log.action%type;

Begin

if inserting then

l_action:=’Insert’;

elsif updating then

l_action:=’Update’;

elsif deleting then

l_action:=’Delete’;

else

raise_application_error(-20001,’You should never ever get this error.’);

Insert into employees_log(Who,action,when)

Values( user, l_action,sysdate);

End;

/

3、 测试

insert into employees_copy( employee_id, last_name, email, hire_date, job_id)

       values(12345,’Chen’,’Donny@hotmail’,sysdate,12);

select *from employees_log

三、ORACLE Tiger触发器实例

实例1

--创建触发器，当用户对test表执行DML语句时，将相关信息记录到日志表

--创建测试表

CREATE TABLE test

(

t_id   NUMBER(4),

t_name VARCHAR2(20),

t_age NUMBER(2),

t_sex CHAR

);

--创建记录测试表

CREATE TABLE test_log

(

l_user   VARCHAR2(15),

l_type   VARCHAR2(15),

l_date   VARCHAR2(30)

);

--创建触发器

CREATE OR REPLACE TRIGGER test_trigger

AFTER DELETE OR INSERT OR UPDATE ON test

DECLARE

v_type test_log.l_type%TYPE;

BEGIN

IF INSERTING THEN --INSERT触发

   v_type := 'INSERT';

   DBMS_OUTPUT.PUT_LINE('记录已经成功插入，并已记录到日志');

ELSIF UPDATING THEN --UPDATE触发

   v_type := 'UPDATE';

   DBMS_OUTPUT.PUT_LINE('记录已经成功更新，并已记录到日志');

ELSIF DELETING THEN

   v_type := 'DELETE';

   DBMS_OUTPUT.PUT_LINE('记录已经成功删除，并已记录到日志');

END IF;

INSERT INTO test_log VALUES(user,v_type, TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss'));

END;

/

--下面我们来分别执行DML语句

INSERT INTO test VALUES(101,'zhao',22,'M');

UPDATE test SET t_age = 30 WHERE t_id = 101;

DELETE test WHERE t_id = 101;

--然后查看效果

SELECT * FROM test;

SELECT * FROM test_log;

实例2

--创建触发器，它将映射emp表中每个部门的总人数和总工资

--创建映射表

CREATE TABLE dept_sal

    AS

    SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal

FROM emp

GROUP BY deptno;

DESC dept_sal;

--创建触发器

CREATE OR REPLACE TRIGGER emp_info

AFTER INSERT OR UPDATE OR DELETE ON emp

DECLARE

CURSOR cur_emp IS

SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal

FROM emp

GROUP BY deptno;

BEGIN

 DELETE dept_sal;  --触发时首先删除映射表信息

 FOR v_emp IN cur_emp LOOP

   --DBMS_OUTPUT.PUT_LINE(v_emp.deptno || v_emp.total_emp || v_emp.total_sal);

   --插入数据

   INSERT INTO dept_sal

    VALUES(v_emp.deptno,v_emp.total_emp,v_emp.total_sal);

 END LOOP;

END;

/

--对emp表进行DML操作

INSERT INTO emp(empno,deptno,sal) VALUES('123','10',10000);

SELECT * FROM dept_sal;

DELETE EMP WHERE empno=123;