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.sal<old.sal OR new.sal>old.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;