Oracle表级触发器语句

孟子辰 2016-12-04 06:22

示例一、只有在每个月的10日才允许办理，新员工入职与离职，其他时间不允许增加和删除员工数据

--建立表

CREATE TABLE myemp AS SELECT * FROM emp;

--创建触发器

create or replace trigger changemyemp_trigger

before INSERT OR DELETE on myemp

declare

v_curdate Varchar2(20);

BEGIN

SELECT to_char(SYSDATE,'dd') INTO v_curdate FROM dual;

IF trim(v_curdate)<>'10' THEN

Raise_application_error(-20003,'在每个月的10号才允许办理入职和离职手续');

END IF;

end changemyemp_trigger;

--向表中增加或者删除数据

DECLARE

BEGIN

-- INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(8888,'Test','CLERK',7369,SYSDATE,8000,NULL,10);

DELETE FROM myemp WHERE empno=7369;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line(SQLERRM);

END;

示例二、周末及每天下班时间（每天9：00以前，18：00以后）不允许更新myemp表

--创建触发器

create or replace trigger changemyemp_trigger

before INSERT OR DELETE on myemp

declare

v_curhour Varchar2(20);

v_week VARCHAR2(20);

BEGIN

SELECT to_char(SYSDATE,'day'),to_char(SYSDATE,'hh24') INTO v_week,v_curhour FROM dual;

IF trim(v_week) IN('星期六','星期日') THEN

Raise_application_error(-20003,'周末不允许更新myemp表');

ELSIF TRIM(v_curhour)<'9'OR TRIM(v_curhour)>'18' THEN

Raise_application_error(-20004,'在下班时间不允许更新myemp表');

END IF;

end changemyemp_trigger;

--向表中增加或者删除数据

DECLARE

BEGIN

-- INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(8888,'Test','CLERK',7369,SYSDATE,8000,NULL,10);

DELETE FROM myemp WHERE empno=7369;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line(SQLERRM);

END;

--示例三、每一个员工都在根基本工资收入缴税，2000以下3%，2000~5000，8%，5000以上10%，

--要求建立一张新的表来存放，员工编号，姓名，工资佣金，上缴的税，并且每次在修改员工表中的SAL和COMM字段后自动更新记录

--创建myemp_tax表

CREATE TABLE myemp_tax(

empno NUMBER(4),

ename VARCHAR2(10),

sal NUMBER(7,2),

comm NUMBER(7,2),

tax NUMBER(7,2),

CONSTRAINT pk_myempno PRIMARY KEY(empno),

CONSTRAINT fk_myempno FOREIGN KEY(empno) REFERENCES myemp(empno) ON DELETE CASCADE

);

--创建触发器

create or replace trigger myemp_out

after INSERT OR UPDATE OR DELETE on myemp

declare

PRAGMA AUTONOMOUS_TRANSACTION; --触发器自主事务

CURSOR cur_myemp IS SELECT * FROM myemp; --定义游标找到每行的记录

v_sal myemp.sal%TYPE; --定义变量计算收入

v_myemptax myemp_tax.tax%TYPE; --税收

v_myemp myemp%ROWTYPE;

BEGIN

DELETE FROM myemp_tax; --清空myemp_tax表;

FOR v_myemp IN cur_myemp LOOP

v_sal:=v_myemp.sal+nvl(v_myemp.comm,0); --计算总工资

IF v_sal<2000 THEN

v_myemptax:=v_sal*0.03; --上缴税3%

ELSIF v_sal BETWEEN 2000 AND 5000 THEN

v_myemptax:=v_sal*0.08; --上缴税8%

ELSIF v_sal>5000 THEN

v_myemptax:=v_sal*0.1; --上缴税10%

END IF;

INSERT INTO myemp_tax(empno,ename,sal,comm,tax)

VALUES(v_myemp.empno,v_myemp.ename,v_myemp.sal,v_myemp.comm,v_myemptax);

END LOOP;

COMMIT;

end myemp_out;

--向myemp表中增加一条的记录，然后查询myemp_tax表

INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(8898,'Test','CLERK',7369,SYSDATE,800,100,10);

SELECT * FROM myemp_tax;