Oracle触发器trigger详解

create or replace trigger insertStaffHint
after insert on xgj_test
for each row
declare
— local variables here
begin

dbms_output.put_line(‘新增员工成功’);

end insertStaffHint;

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}…]
ON [schema.]table_name | [schema.]view_name
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;

BEFORE INSERT
BEFORE INSERT FOR EACH ROW
AFTER INSERT
AFTER INSERT FOR EACH ROW

BEFORE UPDATE
BEFORE UPDATE FOR EACH ROW
AFTER UPDATE
AFTER UPDATE FOR EACH ROW

BEFORE DELETE
BEFORE DELETE FOR EACH ROW
AFTER DELETE
AFTER DELETE FOR EACH ROW

/**
非工作时间（星球六 星期日， 非9点~18点的区间）
禁止写入数据

首先要搞清楚： 触发器的类型–语句级触发器。
不管插入了多少条数据，没有必要对每一行数据都进行校验，只要不在这个时间段内，都不让插入。
*/
create or replace trigger addStafffCheck
before insert on xgj_test

declare
— local variables here
begin

if to_char(sysdate, ‘day’) in (‘星期六’, ‘星期日’) or
to_number(to_char(sysdate, ‘hh24′)) not between 9 and 18 then
–禁止insert
raise_application_error(-20001,’非工作时间禁止插入数据’);
end if;
end addStafffCheck;

/**
涨后的薪水不能低于涨前的薪水

1 :old 和 :new 代表同一条记录
2 :old 代表操作该行之前，这一行的值
:new 代表操作该行之后，这一行的值
*/

create or replace trigger checkSalary
before update
on xgj_test
for each row
declare
— local variables here 没有变量声明的话，declare可以省略
begin

— if 涨后的薪水 < 涨前的薪水 then 如何表示呢 ？
if :new.sal < :old.sal then

raise_application_error(-20002,’涨后的薪水:’|| :new.sal ||’小于涨前的薪水：’||:old.sal);
end if;
end checkSalary;

create table xgj_record(info varchar2(256)) ;
create or replace trigger addrecord
after update
on xgj_test
for each row
declare
— local variables here
begin

if :new.sal > 6000 then
insert into xgj_record values(:new.sal ||’-‘|| :new.username ||’-‘|| :new.job);
end if;

end addrecord;

create table xgj_test_bak as select *　from xgj_test ;

create or replace trigger databack
after update
on xgj_test
for each row

begin

update xgj_test_bak set sal = :new.sal where username = :new.username ;

end databack;

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;
CREATE OR REPLACE TRIGGER tr_del_emp
BEFORE DELETE –指定触发时机为删除操作前触发
ON scott.emp
FOR EACH ROW –说明创建的是行级触发器
BEGIN
–将修改前数据插入到日志记录表 del_emp ,以供监督使用。
INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
END;
DELETE emp WHERE empno=7788;
DROP TABLE emp_his;
DROP TRIGGER del_emp;

CREATE OR REPLACE TRIGGER tr_dept_time
BEFORE INSERT OR DELETE OR UPDATE
ON departments
BEGIN
IF (TO_CHAR(sysdate,’DAY’) IN (‘星期六’, ‘星期日’)) OR (TO_CHAR(sysdate, ‘HH24:MI’) NOT BETWEEN ’08:30′ AND ’18:00′) THEN
RAISE_APPLICATION_ERROR(-20001, ‘不是上班时间，不能修改departments表’);
END IF;
END;

CREATE OR REPLACE TRIGGER tr_emp_sal_comm
BEFORE UPDATE OF salary, commission_pct
OR DELETE
ON HR.employees
FOR EACH ROW
WHEN (old.department_id = 80)
BEGIN
CASE
WHEN UPDATING (‘salary’) THEN
IF :NEW.salary < :old.salary THEN

RAISE_APPLICATION_ERROR(-20001, ‘部门80的人员的工资不能降’);
END IF;
WHEN UPDATING (‘commission_pct’) THEN

IF :NEW.commission_pct < :old.commission_pct THEN
RAISE_APPLICATION_ERROR(-20002, ‘部门80的人员的奖金不能降’);
END IF;
WHEN DELETING THEN
RAISE_APPLICATION_ERROR(-20003, ‘不能删除部门80的人员记录’);
END CASE;
END;

/*
实例：
UPDATE employees SET salary = 8000 WHERE employee_id = 177;
DELETE FROM employees WHERE employee_id in (177,170);
*/

CREATE OR REPLACE TRIGGER tr_reg_cou
AFTER update OF region_id
ON regions
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(‘旧的region_id值是’||:old.region_id
||’、新的region_id值是’||:new.region_id);
UPDATE countries SET region_id = :new.region_id
WHERE region_id = :old.region_id;
END;

CREATE OR REPLACE PROCEDURE add_job_history
( p_emp_id job_history.employee_id%type
, p_start_date job_history.start_date%type
, p_end_date job_history.end_date%type
, p_job_id job_history.job_id%type
, p_department_id job_history.department_id%type
)
IS
BEGIN
INSERT INTO job_history (employee_id, start_date, end_date,
job_id, department_id)
VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;

–创建触发器调用存储过程…
CREATE OR REPLACE TRIGGER update_job_history
AFTER UPDATE OF job_id, department_id ON employees
FOR EACH ROW
BEGIN
add_job_history(:old.employee_id, :old.hire_date, sysdate,
:old.job_id, :old.department_id);
END;