oracle数据库支持的触发器,Oracle数据库---触发器

SQL> --当我们对empnew执行删除操作之后，它就会出现一个提示信息，提示：这是删除操作！

SQL> CREATE TRIGGER first_trigger

2 AFTER DELETE

3 ON empnew

4 BEGIN

5 DBMS_OUTPUT.put_line('这是删除的操作！');

6 END;

7 /

Trigger created

SQL> SET SERVEROUTPUT ON

SQL> DELETE FROM empnew WHERE empno = 7788;

这是删除的操作！

1 row deleted

--案例1：禁止SCOTT用户的DDL操作

CREATE OR REPLACE TRIGGER scott_trigger

BEFORE DDL

ON SCHEMA

BEGIN

RAISE_APPLICATION_ERROR(-20005,'scott用户禁止所有的DDL操作！');

END;

--测试

CREATE SEQUENCE test_seq;

--案例2：

--创建一个记录数据库对象DDL操作的日志表

create table object_log(

logid number constraint pk_logid primary key,

operatedate date not null，

objecttype varchar2(50) not null,

objectowner varchar2(50) not null

);

CREATE SEQUENCE object_log_seq;

--创建触发实现对数据库对象DDL操作记录的触发器

CREATE OR REPLACE TRIGGER object_trigger

AFTER CREATE OR DROP OR ALTER

ON DATABASE

BEGIN

INSERT INTO object_log(logid,operatedate,objecttype,objectowner)

VALUES(object_log_seq.nextval,sysdate,ORA_DICT_OBJ_TYPE,ORA_DICT_OBJ_OWNER);

END;

--测试

conn yanln/yanln

create sequence test_seq1;

conn system/password

select * from object_log;

--开发示例1

SQL> CREATE OR REPLACE TRIGGER emp_trigger1

2 BEFORE INSERT OR UPDATE OR DELETE

3 ON emp

4 BEGIN

5 IF to_char(sysdate,'day') IN ('星期六','星期日')THEN

6 RAISE_APPLICATION_ERROR(-20006,'不能在休息日改变员工信息！');

7 END IF;

8 END;

9 /

Trigger created

SQL> delete from emp where empno = 7788;

delete from emp where empno = 7788

ORA-20006: 不能在休息日改变员工信息！

ORA-06512: 在 "SCOTT.EMP_TRIGGER1", line 3

ORA-04088: 触发器 'SCOTT.EMP_TRIGGER1' 执行过程中出错

--开发示例2

--创建审计表

CREATE TABLE delete_emp_audit(

name VARCHAR2(10),

delete_time DATE

);

--创建触发器

CREATE OR REPLACE TRIGGER del_emp_trigger

AFTER DELETE ON emp

FOR EACH ROW

BEGIN

INSERT INTO delete_emp_audit VALUES(:old.ename,SYSDATE);

END;

--测试

DELETE FROM emp WHERE empno = 7499;

select * from delete_emp_audit;

--开发示例3

SQL> CREATE OR REPLACE TRIGGER tr_check_sal

2 BEFORE UPDATE OF sal ON emp

3 FOR EACH ROW

4 WHEN (new.salold.sal*1.5)

5 BEGIN

6 RAISE_APPLICATION_ERROR(-20028,'工资只升不降，并且升幅不能超过50%');

7 END;

8 /

Trigger created

SQL> UPDATE emp SET sal = sal*1.8 WHERE empno = 7788;

UPDATE emp SET sal = sal*1.8 WHERE empno = 7788

ORA-20028: 工资只升不降，并且升幅不能超过50%

ORA-06512: 在 "SCOTT.TR_CHECK_SAL", line 2

ORA-04088: 触发器 'SCOTT.TR_CHECK_SAL' 执行过程中出错

--开发示例4

CREATE OR REPLACE TRIGGER upd_cascade_trigger

AFTER UPDATE OF deptno

ON dept

FOR EACH ROW

BEGIN

UPDATE emp SET deptno = :new.deptno WHERE deptno = :old.deptno;

END;

--测试

UPDATE dept SET deptno = 50 WHERE deptno = 10;

select deptno,ename from emp where deptno = 50;

--创建视图

CREATE OR REPLACE VIEW emp_dept

AS

SELECT d.deptno,d.dname,e.empno,e.ename

FROM dept d, emp e

WHERE d.deptno = e.deptno;

--创建替代触发器

CREATE OR REPLACE TRIGGER instead_of_trigger

INSTEAD OF

INSERT

ON emp_dept

FOR EACH ROW

DECLARE

v_temp INT;

BEGIN

SELECT COUNT(*) INTO v_temp FROM dept WHERE deptno = :new.deptno;

IF v_temp = 0 THEN

INSERT INTO dept(deptno,dname)VALUES(:new.deptno,:new.dname);

END IF;

SELECT COUNT(*) INTO v_temp FROM emp WHERE empno = :new.empno;

IF v_temp = 0 THEN

INSERT INTO emp(empno,ename,deptno) VALUES(:new.empno,:new.ename,:new.deptno);

END IF;

END;

--测试

INSERT INTO emp_dept VALUES(50,'DEVELOPMENT',2222,'ALICE');

SELECT * FROM EMP_DEPT

--连接sys用户

--创建事件表

CREATE TABLE event_table(

event varchar2(50),

event_time date

);

--再创建一个系统触发器

create or replace trigger startup_trigger

after startup on database

begin

insert into event_table values(ora_sysevent,SYSDATE);

end;

--在SQLPLUS窗口执行下列命令

SHUTDOWN

STARTUP

SELECT * FROM event_table;

内容来源于网络如有侵权请私信删除