oracle触发器trigger：oracle20(跟着宝哥学java:oracle系列：全网最全)：oracle触发器trigger，ddl触发器、dml触发器、行级触发器、语句级触发器

--oracle面试题
--1 oracle和mysql的区别
--2 数据库优化
--3 oracle的导入导出
--4 oracle和mysql实现分页
--5 oracle序列
--6 函数和存储过程的区别
--7 存储过程和触发器的区别
--8 什么是视图  是否可以对视图解析增删改
--9 索引 
/*
触发器：trigger:可以自动执行的存储过程
        当指定事件发生时  自动执行的plsql块
注意：触发器不能被主动调用
作用：
    --自动生成数据
    --自定义复杂的安全权限
    --提供审计和日志记录
    --启用复杂的业务逻辑
分类：根据事件不同分类
    -- ddl(模式)触发器：对表进行创建 修改表结构 删除表
    -- dml触发器：执行dml语句：delete update insert
         --- 行级触发器:   dml语句执行时影响几行 触发器就执行多少次
         --- 语句级触发器: dml语句执行一次  触发器只执行一次
    -- 数据库级触发器： (略)
        登录 退出 创建连接 关闭连接等事件时执行的触发器  
        
注意1：dml触发器 有三个boolean属性updating,deleting,inserting 获取当前dml语句的类型
格式1：
    --dml语句级触发器
      create or replace trigger
          触发器名
      after|before  insert[or delete or update] on 表名
      [declare 定义变量 ]
      begin
          plsql块
      end;    
      
格式2：
    --dml行级触发器
      create or replace trigger
          触发器名
      after|before  insert[or delete or update] on 表名
      for each row
      [declare 定义变量 ]
      begin
          plsql块
      end;  
注意2：行级触发器中有两个变量:old和:new 分别记录的是触发器执行前后的行的信息                           
*/
grant dba to tian;
create table stu(
   Sno  varchar(50)  primary key,
   Sname  varchar(50)  not null,
   Sage  int   check(sage >=18 and Sage <=40),
   Sbirth  date  not null,
   address  varchar(50)  default '地址不详',
   sex char(3)
);
create sequence seq_stu;
insert into stu values(
   seq_stu.nextval,
   dbms_random.string('i',4),
   trunc(dbms_random.value(18,41),0),
   to_date(trunc(dbms_random.value(1990,2010),0)||'-'||trunc(dbms_random.value(1,13),0)||'-'||trunc(dbms_random.value(1,32),0),'yyyy-mm-dd'),
   dbms_random.string('p',4),
   '女'
);
update student set sage=sage+1 where sex='男';
delete from student where sage=20;
delete from student where sage<=20;
select * from student;
-- dml触发器  -- 语句级触发器：：：一个sql语句 触发器只执行一次
create or replace trigger
   tri_1
before insert or update or delete on student
begin
  if updating then
     dbms_output.put_line('您执行了update语句：：：');
  end if;
  if deleting then
     dbms_output.put_line('您执行了delete语句：：：');
  end if;
  if inserting then
     dbms_output.put_line('您执行了insert语句：：：');
  end if;
end;

-- dml触发器  -- 行级触发器：：：sql语句影响多少行  触发器就执行多少次
--               :old  获取行的旧信息
--               :new  获取行的新信息
create or replace trigger
   tri_2
before insert or update or delete on student
for each row
begin
  if updating then
     dbms_output.put_line('您执行了update语句：：：旧的信息：'||:old.sno||'::'||:old.sname||'::'||:old.sage);
     dbms_output.put_line('您执行了update语句：：：新的信息：'||:new.sno||'::'||:new.sname||'::'||:new.sage);
  end if;
  if deleting then
     dbms_output.put_line('您执行了delete语句：：：旧的信息：'||:old.sno||'::'||:old.sname||'::'||:old.sage);
  end if;
  if inserting then
     dbms_output.put_line('您执行了insert语句：：：新的信息：'||:new.sno||'::'||:new.sname||'::'||:new.sage);
  end if;
end;


-- ddl触发器(模式触发器)  了解
-- 创建一个表记录操作的信息
 CREATE TABLE obj (
          oname VARCHAR2(30),
          otype VARCHAR2(20),
          odate DATE
 );
-- 创建触发器：当前用户执行ddl语句时 触发
CREATE OR REPLACE TRIGGER t_4
AFTER drop or create or alter  ON SCHEMA    
declare
   un varchar(100); 
begin
  select USERNAME into un from user_users;
  dbms_output.put_line(un||'执行了ddl语句！');
  insert into  obj  values(ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);
end;
create sequence seq_0;
drop sequence seq_0;
create view v_1 as select * from student;
create table tab_1(id int primary key,name char(11));
select * from obj;
/*
ddl触发器可以使用的属性：
ORA_CLIENT_IP_ADDRESS	客户端IP地址
ORA_DATABASE_NAME	数据库名称

ORA_DES_ENCRYPTED_PASSWORD	当前用户的DES算法加密后的密码
ORA_DICT_OBJ_NAME	触发DDL的数据库对象名称
ORA_DICT_OBJ_NAME_LIST	受影响的对象数量和名称列表
ORA_DICT_OBJ_OWNER	触发DDL的数据库对象属主
ORA_DICT_OBJ_OWNER_LIST	受影响的对象数量和名称列表
ORA_DICT_OBJ_TYPE	触发DDL的数据库对象类型
ORA_GRANTEE	被授权人数量
ORA_INSTANCE_NUM	数据库实例数量
ORA_IS_ALTER_COLUMN	如果操作的参数column_name指定的列，返回true,否则false
ORA_IS_CREATING_NESTED_TABLE	如果正在创建一个嵌套表则返回true,否则false
ORA_IS_DROP_COLUMN	如果删除的参数column_name指定的列，返回true,否则false
ORA_LOGIN_USER	触发器所在的用户名
ORA_PARTITION_POS	SQL命令中可以正确添加分区子句位置
ORA_PRIVILEGE_LIST	授予或者回收的权限的数量。
ORA_REVOKEE	被回收者的数量
ORA_SQL_TXT	触发了触发器的SQL语句的行数。
ORA_SYSEVENT	导致DDL触发器被触发的时间
ORA_WITH_GRANT_OPTION	如果授权带有grant选项，返回true。否则false
*/

--  实现一个触发器：：对student表进行监控：
--  再创建一个表studentinfo表：字段包含student的所有字段：username+executeDate+executeType
--  对student表进行dml时  在studentinfo表中自动生成一行记录：

-- 查询当前用户的所有触发器
select  * from user_triggers;
-- 删除触发器
drop trigger t_4;
-- 禁用触发器
alter trigger tri_1 disable;
-- 启用触发器
alter trigger tri_1 enable;


-- 练习
-- 查看student表的表结构
select * from user_tab_cols where TABLE_NAME='STUDENT';
select * from student;
create table studentInfo(
  sid int primary key,
  sno int,               -- 学生学号
  sname varchar(200),
  sage int,
  sbirth date,
  address varchar(200),
  sex char(3),
  userName varchar(100),   -- 修改的用户
  executeDate timestamp,   -- 修改时间
  executeType  varchar(100) -- 修改类型：删除、添加、修改
);
create sequence seq_stuinfo start with 1000;

-- 创建触发器
create or replace trigger tri_student_info
after update or delete or insert on student
for each row
declare
  uname varchar(100);
begin
  select USERNAME into uname from user_users; 
  if updating then
     dbms_output.put_line('您执行了update语句：：：：'||:old.sno);
     insert into studentInfo values(seq_stuinfo.nextval,:old.sno,:old.sname,:old.sage,:old.sbirth,:old.address,:old.sex,uname,systimestamp,'修改');
  end if;
  if deleting then
     dbms_output.put_line('您执行了delete语句：：：：'||:old.sno);
     insert into studentInfo values(seq_stuinfo.nextval,:old.sno,:old.sname,:old.sage,:old.sbirth,:old.address,:old.sex,uname,systimestamp,'删除');
  end if;
  if inserting then
     dbms_output.put_line('您执行了insert语句：：：：'||:new.sno);
     insert into studentInfo values(seq_stuinfo.nextval,:new.sno,:new.sname,:new.sage,:new.sbirth,:new.address,:new.sex,uname,systimestamp,'添加');
  end if;
end;

select * from tabs;
-- 数据库备份
-- oracle的导入和导出
-- 注意1：必须在cmd中写命令
drop trigger TRI_STUDENT_INFO;
drop trigger TRI_1;
drop trigger TRI_2;
-- 注意2：命令后面不要加;
exp tian/123@ORCL43  tables=(student,stu) file='f:\3.dmp'  --导出指定表
exp tian/123@ORCL43  full=y inctype=complete  file='f:\2.dmp'  --导出整个用户的所有对象和数据
imp miao/123@ORCL43 file='f:\3.dmp'  fromuser=tian TABLES=(student,stu) -- 把指定文件的信息导入到miao用户下