Oracle触发器

--触发器



/*
1 什么是触发器  ： 当你去执行某些SQL命令的时候自动执行

触发器的分类

1 DML ： 更删改  ->对表的操作
    删除数据 我提示你删除
    删除数据我自动记录谁删除的

2 DDL : 对表的操作
    创建表
    删除表
    修改表
    触发器的触发条件

3 数据库事件
    登录数据库
    创建用户
    注销账户
    特定错误消息
    等

*/
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]
BEGIN
END


-- CREATE [OR REPLACE] TRIGGER trigger_name
-- CREATE : 创建
-- [OR REPLACE] ：同名覆盖
-- TRIGGER ： 触发器的关键字
-- trigger_name： 触发器的名称


-- {BEFORE | AFTER }
-- BEFORE ：在执行之前运行
-- AFTER ：在执行之后运行


{INSERT | DELETE | UPDATE 

[OF column [, column …]]}  --DML 的操作

--[OF column [, column …]]}
--指定到列 
/*
ps ：
of sno ,sname

*/

[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]
--触发器条件

BEGIN
END
/*
BEGIN
触发器的函数体
END
*/

-- 删除触发器
drop TRIGGER TRIGGER_name;
/*
drop： 删除操作
TRIGGER ： 触发器的标志
TRIGGER_name ： 触发器的名称
;
*/


SET SERVEROUTPUT ON
-- 单个触发器
 CREATE OR REPLACE TRIGGER DELETE_students
 AFTER DELETE
 ON student
 BEGIN
 if DELETING then
     DBMS_OUTPUT.PUT_LINE('万恶的管理员');
     DBMS_OUTPUT.PUT_LINE('你删除了一条学生信息');
 END if;
 END;
 /

-- 复合型的触发器
CREATE OR REPLACE TRIGGER DELETE_students
AFTER DELETE or INSERT or UPDATE of sname
ON student
BEGIN
if DELETING then
    DBMS_OUTPUT.PUT_LINE('万恶的管理员');  
    DBMS_OUTPUT.PUT_LINE('你删除了一条学生信息');  
END if;
if INSERTing then
    DBMS_OUTPUT.PUT_LINE('管理员插入了一条学生信息');
END if;

if UPDATing then
    DBMS_OUTPUT.PUT_LINE('你修改的学生的姓名');
END if;
END;


insert into student values ('s010','陈美',22,'女');

UPDATE student SET sage = '18' WHERE sno = 's010' ;
UPDATE student SET sname = '陈美丽' WHERE sno = 's010' ;



--触发器的用法
-- 有个人对触发器做了对应的操作
-- 我想保存操作记录 log

CREATE table student_log
(
    -- 这个学生日志表 保存对应的操作记录
sno varchar2(10) ,
sname varchar2(20),
sage number(2),
ssex varchar2(5),
DML_type varchar2(20),
osname varchar2(20),
osage number(2),
ossex varchar2(5)
);


CREATE OR REPLACE TRIGGER student_log_TRIGGER
AFTER DELETE or INSERT or UPDATE
ON student
FOR EACH ROW 
BEGIN
if UPDATing then
    DBMS_OUTPUT.PUT_LINE('你修改了学生信息消息记录于student_log表中');
    insert into student_log  
    values(:old.sno,:new.sname,:new.sage,:new.ssex,'UPDATE',:old.sname,:old.sage,:old.ssex);
END if;
if DELETING then
    insert into student_log(sno,DML_type,osname,osage,ossex) values(:old.sno,'DELETE',:old.sname,:old.sage,:old.ssex) ;
    DBMS_OUTPUT.PUT_LINE('你删除了学生信息消息记录于student_log表中');  
END if;
if INSERTing then
    DBMS_OUTPUT.PUT_LINE('你插入了学生信息消息记录于student_log表中');
insert into student_log(sno,DML_type,sname,sage,ssex)  
values(:new.sno,'INSERT',:new.sname,:new.sage,:new.ssex) ;
 END if;
END;
/

insert into student values ('s011','杨超越',18,'女');
insert into student values ('s012','孙超越',21,'女');

UPDATE student SET sname = '孙不凡' WHERE sno = 's012' ;
DELETE  student WHERE  sno = 's012';

DELETE student WHERE sno='s011';
-- 为什么一直创建失败
-- old ： 原来没有被改变中的表的数据
-- new : 新的值
-- 注意   无论是用：old  或者是 ：new 调用的时候  注意名称！！！



-- SNO                  SNAME                                          SAGE
-- -------------------- ---------------------------------------- ----------
-- SSEX       DML_TYPE
-- ---------- ----------------------------------------
-- OSNAME                                        OSAGE OSSEX
-- ---------------------------------------- ---------- ----------
--            DELETE
-- 杨超越                                           18 女

INSERT into student 
select SNO,OSNAME,OSAGE,OSSEX  from student_log WHERE DML_TYPE='DELETE' and sno='s011';
/*
数据库日志灾备手段之一
*/

    insert into student_log(sno,DML_type,sname,sage,ssex)  
    values('1','1','1','1','1') ;

/*
insert into student values ('s010','陈美',22,'女');

UPDATE student SET sage = '18' WHERE sno = 's010' ;
UPDATE student SET sname = '陈美丽' WHERE sno = 's010' ;
:new : 指代新来的数据
:old ：老数据
*/


--例2：创建触发器，存放有关事件信息。

DESC ora_sysevent
DESC ora_login_user

--创建用于记录事件用的表

CREATE TABLE ddl_event
(crt_date timestamp PRIMARY KEY,
 event_name VARCHAR2(20), 
 user_name VARCHAR2(10),
 obj_type VARCHAR2(20),
 obj_name VARCHAR2(20));

--创建触触发器
CREATE OR REPLACE TRIGGER tr_ddl
AFTER DDL ON SCHEMA
BEGIN
   INSERT INTO ddl_event VALUES
   (systimestamp,ora_sysevent, ora_login_user, 
    ora_dict_obj_type, ora_dict_obj_name);
END tr_ddl;

 

 

--例3：创建登录、退出触发器。

 

 

CREATE TABLE log_event
(user_name VARCHAR2(10),
 address VARCHAR2(20), 
 logon_date timestamp,
 logoff_date timestamp); 

--创建登录触发器
CREATE OR REPLACE TRIGGER tr_logon
AFTER LOGON ON DATABASE
BEGIN
   INSERT INTO log_event (user_name, address, logon_date)
   VALUES (ora_login_user, ora_client_ip_address, systimestamp);
END tr_logon;
--创建退出触发器
CREATE OR REPLACE TRIGGER tr_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
   INSERT INTO log_event (user_name, address, logoff_date)
   VALUES (ora_login_user, ora_client_ip_address, systimestamp);
END tr_logoff;