11、触发器

一.触发器
分类：
(1)行级触发器：DML，如果操作批量数据，会出现多个影响语句
(2)语句级触发器:DML ，如果操作批量数据，会出现一个影响语句
(3)instead of 触发器
(4)模式触发器:DDL
(5)数据库级触发器 :数据库登陆、注销等操作

--行级触发器语法
create  or replace trigger  触发器名字
before|after  （动作update|insert|delete）
on 表名
for each row --行级触发器
begin
    --备份操作（插入删除的数据到备份表）
end;

--快速复制表结构并创建
--where条件为true或不写where条件复制表数据和表结构，
--where条件为false只复制表结构不复制表数据

-- 创建一个删除存放记录的表
create  table del_emp as select * from emp where 1=2；

--1.建立一个行级触发器，当删除某一条记录时，将删除数据插入到某张备份表
create or replace trigger tr_del_emp
   before delete  --触发的时机是删除前触发
   on emp    --指定操作的表
   for each row              --行级触发器
   when (old.deptno<>10)  --触发的条件
begin
   --删除前将数据插入到备份表
   insert into del_emp(deptno,empno,ename,job,mgr,sal,comm,hiredate)
      values( :old.deptno, :old.empno, :old.ename, :old.job, :old.mgr, :old.sal, :old.comm,
       :old.hiredate);
end;
 /

--删除5001数据
delete emp where empno=7902;

--查询备份表
select * from del_emp;

--回滚
rollback；

--删除触发器
drop trigger tr_del_emp; 



--2.创建行级触发器，删除批量数据
create or replace trigger upda_emp_row
  after insert or update or delete
  on emp
  for each row
begin
  if updating then
    dbms_output.put_line('数据已经更新！');
  elsif deleting then
    dbms_output.put_line('数据已经删除！');
  elsif inserting then
    dbms_output.put_line('数据已经插入');
  end if;
end;
/

--删除表中所有记录
delete from emp;

--回滚删除的数据
rollback;

--删除触发器
drop trigger upda_emp_row;

--3.创建语句触发器，删除批量数据
--.语句触发器( 默认触发器)
create or replace trigger upda_emp
  after insert or update or delete
  on emp
begin
   if updating then
      dbms_output.put_line('数据已经更新');
  elsif deleting then
     dbms_output.put_line('数据已删除');
  elsif inserting then
    dbms_output.put_line('数据插入');
  end if;
end;   
/
--删除
delete from emp;

--回滚
rollback;

--删除触发器
drop trigger upda_emp;

conn system/accp

--创建用户
create user user1 identified by user1；

--授权
grant connect,resource to user1;

--切换用户user1
conn user1

--创建记录表
create table event_ddl(event varchar2(20), username varchar2(10),owner varchar2(10),
   objname varchar2(20),objtype varchar2(10),time date);

--4.模式触发器DDL
--创建用户
 create user user1 identified by user1;
 
 --给用户赋权
 grant connect ,resource to user1;
 
 --切换用户登录
 conn user1/user1;
 
 --创建记录表
  create table event_ddl(
   event varchar2(20),
   username varchar2(20),
   owner varchar2(10),
   objnamename varchar2(20),
   objtype varchar2(10),
   time date);
  
--创建触发器记录user1模式的所有ddl的操作
create or replace trigger tr_ddl
after ddl on user1.schema
begin
--ora_sysevent是创建还是删除
--ora_login_user 登录用户
--ora_dict_obj_owner 操作用户
--ora_dict_obj_name 操作对象名称=表名
--ora_dict_obj_type操作类型是表还是其他
  insert into event_ddl values(ora_sysevent,ora_login_user,ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,sysdate);
end;
/

--调用触发器
create table temp(a int);
drop table temp;

--查看结果
select * from event_ddl;
drop table event_ddl purge;

EVENT  USERNAME    OWNER  OBJNAMENAME   OBJTYPE   TIME
----- ----------- ------- ------------  -------   -----
CREATE  USER1      USER1    TEMP       TABLE      13-12月-15

DROP    USER1      USER1    TEMP       TABLE      13-12月-15

CREATE  USER1      USER1    TEMP       TABLE      23-3月 -16

DROP    USER1      USER1    TEMP       TABLE      23-3月 -16

--删除触发器
drop trigger tr_ddl;
   
   
--5.数据库级触发器
--题目：记录所有用户登录和退出数据库的用户名称、登录和退出时间
--用管理员操作
conn system
输入密码：

--创建记录表
create table log_table(
  username varchar2(20), login_time date,
  logoff_time date,address varchar2(20)
  );
  
--创建数据库级触发器
create or replace trigger tr_login
after logon on database
begin
  insert into log_table(username,login_time,address)
    values(ora_login_user,sysdate,ora_client_ip_address);
end;
/

--切换用户登录
conn holly/sys
conn scott/tiger
conn system/sys
--查询记录表
select * from log_table;
USERNAME             LOGIN_TIME     LOGOFF_TIME    ADDRESS
-------------------- -------------- -------------- --------------------
HOLLY                13-12月-15
SCOTT                13-12月-15
SYSTEM               13-12月-15
SYSMAN               13-12月-15

--删除表数据并且不进入回收站
drop table log_table purge;

--删除触发器
drop trigger tr_login;