Oracle中触发器示例详解

CREATE OR REPLACE TRIGGER scott_trigger
BEFORE DDL
ON SCHEMA
BEGIN
 RAISE_APPLICATION_ERROR(-20008,'禁止scott用户的所有ddl操作');
END;

create sequence myseq;

CREATE OR REPLACE TRIGGER emp_trigger
BEFORE UPDATE OR DELETE OR INSERT
ON emp
BEGIN
 IF to_char(sysdate,'day') IN ('星期四','星期五') THEN
 RAISE_APPLICATION_ERROR(-20008,'不允许在周四周五修改emp表');
 END IF;
END;

update emp set sal=800;

create table emp_new
as
select * from emp;

create table emp_audit(name varchar2(10),delete_time Date);

CREATE OR REPLACE TRIGGER delete_trigger
AFTER DELETE ON emp_new
FOR EACH ROW
BEGIN
 INSERT INTO emp_audit values(:old.ename,sysdate);
END;

delete from emp_new where empno='7499';

select * from emp_audit;

create table emp_new
as
select * from emp;

CREATE OR REPLACE TRIGGER emp_trigger
BEFORE UPDATE OF sal ON emp_new
FOR EACH ROW
WHEN (new.sal1.5*old.sal)
BEGIN
 RAISE_APPLICATION_ERROR(-20008,'工资只增不降，且涨幅不可大于50%');
END;

update emp_new set sal = 1.6*sal where empno='7788';

create table emp_new
as
select * from emp;

create table dept_new
as
select * from dept;

CREATE OR REPLACE TRIGGER cascade_trigger
AFTER UPDATE OF deptno ON dept_new
FOR EACH ROW
BEGIN
 UPDATE emp_new SET deptno=:new.deptno WHERE deptno=:old.deptno;
END;

update dept_new set deptno=15 where deptno=20;

select * from dept_new;

select * from emp_new;

CREATE VIEW emp_view
(ename,empno)
AS SELECT ename,empno FROM emp 
WHERE deptno=20
WITH CHECK OPTION;

CREATE TABLE emp_new
AS
SELECT * FROM emp;
CREATE TABLE dept_new
AS
SELECT * FROM dept;

CREATE VIEW emp_dept
AS
SELECT d.deptno,d.dname,e.empno,e.ename
FROM dept_new d,emp_new e
WHERE d.deptno=e.deptno;

grant create view to scott;

CREATE OR REPLACE TRIGGER insteadof_trigger
INSTEAD OF INSERT ON emp_dept
FOR EACH ROW
DECLARE
 v_temp INT;
BEGIN
 SELECT COUNT(*) INTO v_temp FROM dept_new WHERE deptno=:new.deptno;
 IF v_temp=0 THEN
  INSERT INTO dept_new(deptno,dname) VALUES(:new.deptno,:new.dname);
 END IF;
  SELECT COUNT(*) INTO v_temp FROM emp_new WHERE empno=:new.empno;
 IF v_temp=0 THEN
  INSERT INTO emp_new(deptno,empno,ename) VALUES(:new.deptno,:new.empno,:new.ename);
 END IF;
END;

INSERT INTO emp_dept values(15,'HUMANRESOURCE',7999,'LEAF');

select * from emp_new;

select * from dept_new;

CREATE TABLE event_table(event VARCHAR2(50),event_time DATE);

CREATE OR REPLACE TRIGGER event_trigger
AFTER STARTUP ON DATABASE
BEGIN
 INSERT INTO event_table VALUES(ora_sysevent,sysdate);
END;

select * from event_table;

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 obj_log_seq;

CREATE OR REPLACE TRIGGER object_trigger
AFTER CREATE OR DROP OR ALTER ON DATABASE
BEGIN
 INSERT INTO object_log VALUES(obj_log_seq.nextval,sysdate,ora_dict_obj_type,ora_dict_obj_owner);
END;

CREATE SEQUENCE my_seq;

SELECT * FROM object_log;