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.sal<old.sal OR new.sal>1.5*old.sal)
BEGIN
  RAISE_APPLICATION_ERROR(-20008,'工资只增不降，且涨幅不可大于50%');
END;

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

这里可以看到当改变数据时会触发触发器错误，对表中某一个字段的修改用UPDATE OF即可，另外如果new和old在PLSQL块的外部

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;

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;

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;