触发器笔记

-- 创建一个学生表
CREATE TABLE student(
stuID VARCHAR2(10) NOT NULL,
stuName VARCHAR2(10) NOT NULL,
sex CHAR(2)
);


INSERT INTO student VALUES(101, '刘德华', '男');
INSERT INTO student VALUES(102, 'Mike', '男');
INSERT INTO student VALUES(103, 'Mike', '男');
INSERT INTO student VALUES(104, 'Mike', '男');
INSERT INTO student VALUES(1001, '李连杰', '男');

commit;

-- 创建一个INSERT触发器
CREATE OR REPLACE TRIGGER tg_insert
BEFORE INSERT ON student
--FOR EACH ROW -- 行级触发器，默认为表级触发器
BEGIN
DBMS_OUTPUT.PUT_LINE('Insert student...');

END;

-- 执行触发器
INSERT INTO student VALUES(9, 'Tom', '女');
INSERT INTO student VALUES(11, 'Mike', '男');

Insert student...

1 row inserted

-- 表级触发器
CREATE OR REPLACE TRIGGER tg_updatestudent
AFTER UPDATE ON student

BEGIN
DBMS_OUTPUT.PUT_LINE('Update student...');

END;

--执行
UPDATE student SET sex = '女';

Update student...

2 rows updated

-- 行级触发器
CREATE OR REPLACE TRIGGER tg_updatestudent
AFTER UPDATE ON student
FOR EACH ROW

BEGIN
DBMS_OUTPUT.PUT_LINE('Update student...');

END;

UPDATE student SET sex = '女';

Update student...
Update student...

2 rows updated


--

CREATE OR REPLACE TRIGGER tg_insert
BEFORE INSERT ON student
FOR EACH ROW -- 行级触发器，默认为表级触发器
BEGIN
DBMS_OUTPUT.PUT_LINE('Insert student...');
DBMS_OUTPUT.PUT_LINE('new student id: ' || :new.stuID);
DBMS_OUTPUT.PUT_LINE('new student name: ' || :new.stuName);
DBMS_OUTPUT.PUT_LINE('new student sex: ' || :new.sex);
END;

-- 执行触发器
SQL> INSERT INTO student VALUES(101, '刘德华', '男');

Insert student...
new student id101
new student name: 刘德华
new student sex:男

1 row inserted

-- 显示当前用户触发器
DESC user_trigger;

-- 查找特定用户的触发器
SELECT trigger_name FROM user_triggers WHERE table_name = upper('student');

-- 创建 DELETE 触发器
CREATE OR REPLACE TRIGGER tg_deletestudent
BEFORE DELETE ON student
FOR EACH ROW -- 行级触发器，默认为表级触发器
BEGIN
DBMS_OUTPUT.PUT_LINE('Delete student...');
DBMS_OUTPUT.PUT_LINE('old student id: ' || :old.stuID);
DBMS_OUTPUT.PUT_LINE('old student name: ' || :old.stuName);
DBMS_OUTPUT.PUT_LINE('old student sex: ' || :old.sex);
END;

-- 执行
SQL> DELETE FROM student WHERE stuId = 101;

Delete student...
old student id: 101
old student name: 刘德华
old student sex: 男

1 row deleted

-- 可以使用 ROLLBACK 取消刚才的操作
SQL> ROLLBACK;

Rollback complete


-- 创建 带条件（WHEN）的 DELETE 触发器
CREATE OR REPLACE TRIGGER tg_deletestudent
BEFORE DELETE ON student
FOR EACH ROW -- 行级触发器，默认为表级触发器
WHEN (old.sex = '男') -- 性别为男则执行触发器
BEGIN
DBMS_OUTPUT.PUT_LINE('Delete student...');
DBMS_OUTPUT.PUT_LINE('old student id: ' || :old.stuID);
DBMS_OUTPUT.PUT_LINE('old student name: ' || :old.stuName);
DBMS_OUTPUT.PUT_LINE('old student sex: ' || :old.sex);
END;

--
CREATE TABLE studentlog(
stuId INT,
sutName VARCHAR(10),
sex VARCHAR(2),
flag CHAR(1));

-- 混合类型触发器
CREATE OR REPLACE TRIGGER trgDelStudent
BEFORE INSERT OR UPDATE OR DELETE ON student
FOR EACH Row
BEGIN
IF Inserting THEN
INSERT INTO studentlog
VALUES(:new.stuId, :new.stuName, :new.sex, 'I');
END IF;

IF DELETING THEN
INSERT INTO studentlog
VALUES(:old.stuId, :old.stuName, :old.sex, 'D');
END IF;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( SQLCODE || ' ' || SQLERRM );

END;

-- 执行结果
SQL> INSERT INTO student VALUES(1001, '李连杰', '男');

Insert student...
new student id: 1001
new student name: 李连杰
new student sex: 男

1 row inserted

SQL> select * from studentlog;

STUID SUTNAME SEX FLAG
--------------------------------------- ---------- --- ----
1001 李连杰 男 I


-- 执行删除，触发对应的语句
SQL> DELETE FROM student WHERE stuId = 1001;

Delete student...
old student id: 1001
old student name: 李连杰
old student sex: 男

1 row deleted

SQL> select * from studentlog;

STUID SUTNAME SEX FLAG
--------------------------------------- ---------- --- ----
1001 李连杰 男 I
1001 李连杰 男 D


-- 下面是库存表
CREATE TABLE GoodsStore(
goodNo INT PRIMARY KEY, --库存商品编号
goodsCount INT --库存商品数量
);

-- 下面是商品出库表
CREATE TABLE GoodsStoreOut(
goodNo INT,
goodsCount INT,
goodsOutDate DATE DEFAULT sysdate --出库日期
);

INSERT INTO GoodsStore VALUES(101, 100);
INSERT INTO GoodsStore VALUES(102, 300);
INSERT INTO GoodsStore VALUES(103, 500);
INSERT INTO GoodsStore VALUES(104, 600);
INSERT INTO GoodsStore VALUES(105, 200);

-- 编号为101的商品出库 20 件
INSERT INTO goodsStoreOut VALUES(101, 20, default);


SQL> INSERT INTO GoodsStore VALUES(101, 100);

1 row inserted

SQL> INSERT INTO GoodsStore VALUES(102, 300);

1 row inserted

SQL> INSERT INTO GoodsStore VALUES(103, 500);

1 row inserted

SQL> INSERT INTO GoodsStore VALUES(104, 600);

1 row inserted

SQL> INSERT INTO GoodsStore VALUES(105, 200);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from goodsstore;

GOODNO GOODSCOUNT
--------------------------------------- ---------------------------------------
101 100
102 300
103 500
104 600
105 200

SQL> INSERT INTO goodsStoreOut VALUES(101, 20, default);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from goodsstoreout;

GOODNO GOODSCOUNT GOODSOUTDATE
--------------------------------------- --------------------------------------- ------------
101 20 2006-7-12 22


-- 定义触发器，完成自动出库
CREATE OR REPLACE TRIGGER tg_GoodsOutStore
AFTER INSERT ON GoodsStoreOut
FOR EACH ROW

BEGIN
UPDATE GoodsStore SET GoodsCount = GoodsCount - :new.goodsCount
WHERE goodNo = :new.goodNo;
DBMS_OUTPUT.PUT_LINE('本次出库的商品编号为： ' || :new.goodNo || ' '|| '出库数量为：' || TO_CHAR(:new.goodsCount) );

END;


SQL> INSERT INTO goodsStoreOut VALUES(102, 30, sysdate);

本次出库的商品编号为： 102 出库数量为：30

1 row inserted

SQL> SELECT * FROM goodsStore;

GOODNO GOODSCOUNT
--------------------------------------- ---------------------------------------
101 100
102 270
103 500
104 600
105 200



-- 创建课程表
CREATE TABLE subject(
subjectId INT,
subjectName VARCHAR2(20)
);

--
CREATE TABLE score(
stuId INT,
subjectId INT,
score INT
);

SQL> INSERT INTO subject VALUES(1, 'Oracle');

1 row inserted

SQL> INSERT INTO subject VALUES(2, 'Java');

1 row inserted

SQL> commit;

CREATE OR REPLACE VIEW stuSubScore AS
SELECT stu.stuId, stu.stuName, sub.subjectId, sub.subjectName, sc.score
FROM student stu, subject sub, score sc
WHERE stu.stuId = sc.stuId AND sc.subjectId = sub.subjectId;


-- 创建替代触发器
CREATE OR REPLACE TRIGGER trgInsertView
INSTEAD OF INSERT ON stuSubScore
REFERENCING new N
FOR EACH ROW

DECLARE
iCount INT;

BEGIN
SELECT count(*) INTO iCount FROM student WHERE stuId = :N.stuId;
IF iCount = 0 THEN
INSERT INTO student(stuid, stuname) VALUES(:N.stuId, :N.stuName);
ELSE
UPDATE student SET student.stuname = :N.stuName WHERE stuID = :N.stuId;
END IF;

SELECT count(*) INTO iCount FROM subject WHERE subjectId = :N.subjectId;
IF iCount = 0 THEN
INSERT INTO subject VALUES(:N.subjectId, :N.subjectName);
ELSE
UPDATE subject SET subject.subjectname = :N.subjectName WHERE subjectID = :N.subjectId;
END IF;

SELECT count(*) INTO iCount FROM score WHERE subjectId = :N.subjectId;
IF iCount = 0 THEN
INSERT INTO score VALUES(:N.stuId, :N.subjectID, :N.score);
ELSE
UPDATE score SET score.score = :N.score WHERE subjectID = :N.subjectId
AND stuId = :N.stuId;
END IF;

END;

-- 变异表
CREATE TABLE emp_table AS SELECT * FROM emp;

-- 创建触发器
CREATE OR REPLACE TRIGGER emp_count
AFTER DELETE ON emp_table
FOR EACH ROW

DECLARE
n INTEGER;
BEGIN
SELECT count(*) INTO n FROM emp_table; -- 使用这个函数，会产生变异表
DBMS_OUTPUT.PUT_LINE('There are now ' || n);

END;

SQL> select * from emp_table;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10

14 rows selected

SQL> delete from emp_table;

delete from emp_table

ORA-04091: 表 SCOTT.EMP_TABLE 发生了变化，触发器/函数不能读
ORA-06512: 在"SCOTT.EMP_COUNT", line 4
ORA-04088: 触发器 'SCOTT.EMP_COUNT' 执行过程中出错

-- 触发器维护
ALTER TRIGGER tg_goodsOutStore ENABLE;
ALTER TRIGGER tg_goodsOutStore DISABLE;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/77580/viewspace-212769/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/77580/viewspace-212769/