存储过程和触发器-行触发器

/*
行触发器在受影响的每一行上执行，可以使用Create Trigger语句创建语句触发器语法如下
CREATE TRIGGER 
[BEFORE|AFTER]  ON 
FOR EACH ROW
<pl>
*/</pl>
--创建两个表，做未试验用
DROP TABLE DEPARTMENT;
DROP TABLE EMPLOYEES;
DROP TRIGGER TESTROWTRIGGER1;
DROP TRIGGER TESTROWTRIGGER2;
CREATE TABLE DEPARTMENT(
	DEPARTID NUMBER(3) NOT NULL UNIQUE,
	DEPARTNAME VARCHAR2(20),
	EMPNUM NUMBER(3)
);
CREATE TABLE EMPLOYEES(
	EMPID NUMBER(3) NOT NULL,
	EMPNAME VARCHAR2(20),
	DEPARTID NUMBER(3) NOT NULL,
	EMPAGE NUMBER(3)
);
/*
目标：
1、当EMPLOTEES有增加的时候，DEPARTMENT中相应的EMPNUM需要增减
2、当DEPARTMENT中的DEPARTID修改的时候，需要修改EMPLOYEES中的DEPARTID以保证数据的一致性
*/
--语句触发器
CREATE OR REPLACE TRIGGER TESTROWTRIGGER1
AFTER INSERT OR DELETE ON EMPLOYEES
DECLARE
	N_NUM NUMBER(5);
	CURSOR CUR_DEPART IS
	SELECT DEPARTID
	FROM DEPARTMENT;
	REC_DEPART DEPARTMENT.DEPARTID%TYPE;
BEGIN
	OPEN CUR_DEPART;
		LOOP
			FETCH CUR_DEPART INTO REC_DEPART;
			EXIT WHEN CUR_DEPART%NOTFOUND;
				SELECT COUNT(1) INTO N_NUM FROM EMPLOYEES WHERE DEPARTID=REC_DEPART;
				UPDATE DEPARTMENT SET EMPNUM = N_NUM WHERE DEPARTID=REC_DEPART;
		END LOOP;
	CLOSE CUR_DEPART;
END;
/
--验证触发器
SQL> INSERT INTO DEPARTMENT VALUES(1,'JIA',0);
已创建 1 行。
SQL> select * from department;

  DEPARTID DEPARTNAME               EMPNUM
---------- -------------------- ----------
         1 JIA                           3
--行触发器
CREATE OR REPLACE TRIGGER TESTROWTRIGGER2
AFTER UPDATE ON DEPARTMENT
FOR EACH ROW
DECLARE
BEGIN
	--:NEW和:OLD是两个虚拟的表
	--:NEW表示执行INSERT,UPDATE,DELETE操作之后的新表
	--:OLD表示执行INSERT,UPDATE,DELETE操作之前的旧表
	UPDATE EMPLOYEES SET DEPARTID = :NEW.DEPARTID WHERE DEPARTID = :OLD.DEPARTID;
END;
/

--验证触发器
SQL> update department set departid=4 where departid=2;
已更新 1 行。
SQL> commit;
--结果
SQL> select * from employees;
     EMPID EMPNAME                DEPARTID     EMPAGE
---------- -------------------- ---------- ----------
         1 CHENZW                        4         28
         1 CHENZW                        4         28
         1 CHENZW                        4         28