笔记081122 --- trigger

87 阅读 0 评论 58 点赞

我是靠谱客的博主寂寞芒果，最近开发中收集的这篇文章主要介绍笔记081122 --- trigger，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

笔记081122 --- trigger

2008-11-21 下午6:32 发布人 tiger chang

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
触发器

触发器的设计是自动执行的，触发器是不接受参数的
可以用来维护表的约束限制，审批表的相关信息，
数据操作员触发器，
数据定义元触发器。

～～～～～～～～～～～～～～～～～～～～～～～～～～～
行级触发器的工作原理
～～～～～～～～～～～～～～～～～～～～～～～～～～～
事务简述
    事务是数据库中的重要机制，用于确保数据完整性和并发处理

的能力
它将一条、一组SQL语句当作成一个逻辑上的单元，用于保证这些语句都

能成功或失败。
事务的特性
    A(AOMICITY):原子性
    C(CONSISTENCY):一致性
    I(ISOLATION):隔离性
    D(DURABILITY):永久性
～～～～～～～～～～～～～～～～～～～～～～～～～～～～
删除数据的行级触发器

CREATE OR REPLEACE TRIGGER del_s
AFTER DELETE ON STUDENT
FOR EACH ROW
BEGIN
DELETE FROM SC WHERE SNO = :OLD.SNO;
END del_s;
/

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`

CREATE OR REPLACE TRIGGER INSERT_S
AFTER INSERT ON STUDENT
FOR EACH ROW
BEGIN
INSERT INTO SC(SNO,CNO) VALUES(NEW.SNO,'0001');
END;
/

OLD AND NEW
old 和 new 是内存中的特殊的表，
old new 是在内存中的逻辑表

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

级联更新

CREATE OR REPLACE TRIGGER UPDATE_S
AFTER UPDATE ON STUDENT
FOR EACH ROW
BEGIN
UPDATE SC SET SNO = :NEW.SNO WHERE SNO= :OLD.SNO;
END;
/
～～～～～～～～～～～～～～～～～～～～～～～～～～～～
触发器中不能使用commit & rollback;

CREATE OR REPLACLE TRIGGER DEL_STU
AFTER DELETE ON STUDENT
FOR EACH ROW
BEGIN
IF :OLD.SNO = '05062233' THEN
RAISE_APPLICATION_ERROR(-20000,'不允许删除该数据');
ENDIF;
END;
/

-------------_______________
SQL> CREATE OR REPLACE TRIGGER DEL_STU
2 AFTER DELETE ON STUDENT
3 FOR EACH ROW
4 BEGIN
5 IF :OLD.SNO = '06062233' THEN
6 RAISE_APPLICATION_ERROR(-20000,'不允许删除该数据');
7 END IF;
8 END;
9 /

触发器已创建

SQL> DELETE FROM STUDENT WHERE SNO = '06062233';
DELETE FROM STUDENT WHERE SNO = '06062233'
            *
第 1 行出现错误:
ORA-20000: 不允许删除该数据
ORA-06512: 在 "CHANGHU.DEL_STU", line 3
ORA-04088: 触发器 'CHANGHU.DEL_STU' 执行过程中出错

_____________________________________________

RAISE_APPLICATION_ERROR(-20000,'不允许删除该数据');
启动错误
(-20999 -20000)这个错误号的使用范围。
---- -20000是错误好，''为错误信息

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

语句级的触发器

～～～～～～～～～～～～～～～～～～～～～～～～～

CREATE TABLE MYLOG(
CURR_USER VARCHAR2(100),
CURR_DATE DATE,
ACT     CHAR(1)
);

CREATE OR REPLACE TRIGGER DML_AA
AFTER INSERT OR DELETE OR UPDATE ON STUDENT
BEGIN
IF INSERTING THEN
    INSERT INTO MYLOG VALUES(USER,SYSDATE,'I');
ELSEIF DELETING THEN
    INSERT INTO MYLOG VALUES(USER,SYSDATE,'D');
ELSE
    INSERT INTO MYLOG VALUES(USER,SYSDATE,'U');
END IF ;
END;
/

SQL> EDIT
已写入 file afiedt.buf

1 CREATE OR REPLACE TRIGGER DEPT2
2 AFTER INSERT OR DELETE OR UPDATE ON MYLOG
3 BEGIN
4 IF INSERTING THEN
5 INSERT INTO MYLOG VALUES(USER,SYSDATE,'I');
6 ELSIF DELETING THEN
7 INSERT INTO MYLOG VALUES(USER,SYSDATE,'D');
8 ELSE
9 INSERT INTO MYLOG VALUES(USER,SYSDATE,'U');
10 END IF;
11* END;
SQL> /

触发器已创建

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CREATE OR REPLACE TRIGGER SET_AUTO
BEFORE INSERT ON AUTO
FOR EACH ROW
DECLARE
NO NUMBER(5),
BEGIN
SELECT MYSEQ.NEXTVAL INTO NO FROM DUAL;
:NEW.A:=NO;
END;
/

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

替换触发器的应用

只能建到试图上

解决试图的多表更新问题

CREATE OR REPLACE TRIGGER TR_V_E_D
INSERT OF INSERT ON view_sc
FOR EACH ROW
BEGIN
INSERT INTO .....
INSERT INTO ......
END;
/

～～～～～～～～～～～～～～～～～～～～～

-------- -----------------------------------------

SQL> L
1 CREATE OR REPLACE TRIGGER DEPT2
2 AFTER INSERT OR DELETE OR UPDATE ON MYLOG
3 BEGIN
4 IF INSERTING THEN
5 INSERT INTO MYLOG VALUES(USER,SYSDATE,'I');
6 ELSEIF DELETING THEN
7 INSERT INTO MYLOG VALUES(USER,SYSDATE,'D');
8 ELSE
9 INSERT INTO MYLOG VALUES(USER,SYSDATE,'U');
10 END IF;
11* END;
SQL> EDIT
已写入 file afiedt.buf

1 CREATE OR REPLACE TRIGGER DEPT2
2 AFTER INSERT OR DELETE OR UPDATE ON MYLOG
3 BEGIN
4 IF INSERTING THEN
5 INSERT INTO MYLOG VALUES(USER,SYSDATE,'I');
6 ELSIF DELETING THEN
7 INSERT INTO MYLOG VALUES(USER,SYSDATE,'D');
8 ELSE
9 INSERT INTO MYLOG VALUES(USER,SYSDATE,'U');
10 END IF;
11* END;
SQL> /

触发器已创建

SQL> DESC DEPT2;
名称                                      是否为空
----------------------------------------- -------

ID                                        NOT NUL
NAME

SQL> INSER INTO DEPT VALUES(4,'HELLOWORLD');
SP2-0734: 未知的命令开头 "INSER INTO..." - 忽略了剩
SQL> INSERT INTO DEPT2 VALUES (4,'HELLOWORLD');

已创建 1 行。

SQL> INSERT INTO DEPT2 VALUES (4,'HELLOWORLD');
INSERT INTO DEPT2 VALUES (4,'HELLOWORLD')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (CHANGHU.SYS_C004113)

SQL> INSERT INTO DEPT2 VALUES (5,'HELLOWORLD');

已创建 1 行。

SQL> INSERT INTO DEPT2 VALUES (6,'HELLOWORLD');

已创建 1 行。

SQL> UPDATE DEPT2 SET NAME = 'GOOGLE' WHERE ID = 5

已更新 1 行。

SQL> UPDATE DEPT2 SET NAME = 'GOOGLE' WHERE ID = 3

已更新 1 行。

SQL> DELETE FROM DEPT2 WHERE ID = 5;

已删除 1 行。

SQL> DELETE FROM DEPT2 WHERE ID = 4;

已删除 1 行。

SQL> SELECT * FROM MYLOG;

未选定行

SQL> SELECT * FROM MYLOG;

未选定行

SQL> SELECT TARGGER_NAME FROM USER_TRIGGERS;
SELECT TARGGER_NAME FROM USER_TRIGGERS
       *
第 1 行出现错误:
ORA-00904: "TARGGER_NAME": 标识符无效

SQL> SELECT TRIGGER_NAME FROM USER_TRIGGERS;

TRIGGER_NAME
--------------------------------------------------
INSERT_S
UPDATE_S
DEL_DEPT2
DEL_S
DEL_STU
DEPT2
ABOUT_MYLOG

已选择7行。

SQL> SELECT * FROM MYLOG;

未选定行

SQL> CREATE OR REPLACE TRIGGER DEPT2
2 AFTER INSERT OR DELETE OR UPDATE ON MYLOG
3 BEGIN
4 IF INSERTING THEN
5 INSERT INTO MYLOG VALUES(USER,SYSDATE,'I');
6 ELSIF DELETING THEN
7 INSERT INTO MYLOG VALUES(USER,SYSDATE,'D');
8 ELSE
9 INSERT INTO MYLOG VALUES(USER,SYSDATE,'U');
10 END IF;
11 END;
12 /

触发器已创建

SQL> EDIT
已写入 file afiedt.buf

1 CREATE OR REPLACE TRIGGER MYLOG
2 AFTER INSERT OR DELETE OR UPDATE ON DEPT2
3 BEGIN
4 IF INSERTING THEN
5 INSERT INTO MYLOG VALUES(USER,SYSDATE,'I');
6 ELSIF DELETING THEN
7 INSERT INTO MYLOG VALUES(USER,SYSDATE,'D');
8 ELSE
9 INSERT INTO MYLOG VALUES(USER,SYSDATE,'U');
10 END IF;
11* END;
SQL> /

触发器已创建

SQL> INSERT INTO DEPT2 VALUES(19,'HELLO');

已创建 1 行。

SQL> SELECT * FROM MYLOG;

CURR_USER                                CURR_DATE      AC
---------------------------------------- -------------- --
CHANGHU                                  22-11月-08     I

SQL> UPDATE DEPT2 SET NAME = 'GOOGLE' WHERE ID = 19;

已更新 1 行。

SQL> SELECT * FROM MYLOG;

CURR_USER                                CURR_DATE      AC
---------------------------------------- -------------- --
CHANGHU                                  22-11月-08     I
CHANGHU                                  22-11月-08     U

SQL> DELETE FROM DEPT2 WHERE ID = 19;

已删除 1 行。

SQL> SELECT * FROM MYLOG;

CURR_USER                                CURR_DATE      AC
---------------------------------------- -------------- --
CHANGHU                                  22-11月-08     I
CHANGHU                                  22-11月-08     U
CHANGHU                                  22-11月-08     D

SQL>