PL/SQL之--触发器

DROP TABLE person ;
CREATE TABLE person (
id NUMBER(11) NOT NULL ,
username VARCHAR2(255 ) NULL ,
age NUMBER(11) NULL ,
password VARCHAR2(255) NULL ,
PRIMARY KEY (id)
)

create or replace trigger trigger_insert
  before insert on person
　referencing new as new old as old
  for each row
declare
  v_username varchar2(50);
begin
  v_username := :new.username; 
  :new.age := :new.age + 2; --插入的年龄加2
  dbms_output.put_line(v_username); -- 插入插入记录的username字段
end trigger_insert; 
--添加数据
insert into person values (1, '张三', 20, 'zhangsan');

create or replace trigger trigger_update_before
  before update on person  
  referencing new as new old as old  
  for each row
declare
  v_username varchar2(50);
begin
  v_username := :new.username; -- 记录新名字
  :new.age := :new.age + 3; -- 修改年龄
  dbms_output.put_line(v_username);
end trigger_update_before;
-- after update 触发器
create or replace trigger trigger_update_after
  before update on person
  referencing new as new old as old
  for each row
declare
  v_old_name varchar2(50);
begin
  v_old_name :=  :old.username;  -- 记录修改前的名字
  dbms_output.put_line(v_old_name);   
end trigger_update_after;    
-- 更新数据
update person set username='张三1', age = 19 where id = 1;

create or replace trigger trigger_delete
  before delete on person
  referencing new as new old as old  
  for each row
declare
  v_old_name varchar2(50);
begin
  v_old_name :=  :old.username;  -- 获取被删除的用户名称
  dbms_output.put_line(v_old_name);
end trigger_delete;
--删除数据
delete from person where id = 1;

-- 记录操作表的信息
create table person_log(
  who varchar2(30),
  when date,
  action varchar2(50)
)
--触发器
create or replace trigger trigger_person_statment
  before insert or update or delete on person
declare
  -- 保存操作信息
  v_action person_log.action%type;
begin
  if inserting then
    v_action := 'Insert';
  elsif updating then
    v_action := 'Update';
  elsif deleting then
    v_action := 'Delete';
  else
   raise_application_error(-20001,'You should never ever get this error.');
  end if;
  insert into person_log(who,action,when)
  values(user, v_action,sysdate);
end trigger_person_statment;
-- 操作shuj
declare
begin  
   insert into person values(1003, '1342', 20, '234234');
   insert into person values(1004, '1342', 20, '234234');
   update person set age = 19 where id >1000 and id <1003;-- 此时触发器之后执行一次
   delete from person where id >1000 and id <1003;-- 此时触发器之后执行一次
end;   

-- 视图
create or replace view v_person as  
  select username, age, password,  id p_id from person; 
-- 触发器
create or replace trigger update_v_person INSTEAD OF 
update on v_person 
begin 
  update person set 
    username = substr(:new.username, instr(:new.username,',')+2),     
    age = :new.age, 
    password = :new.password 
  where id = :new.p_id; -- 视图中p_id字段
end update_v_person; 

-- 尝试更新name 
update person set username = 'Chen1, Donny1', age =18 where id=1
select * from person where id = 1;

-- 准备表登录后保存用户登录信息
create table loggin_event(
    username   varchar2(50),
    logintime  date
); 
-- 登录触发器
create or replace trigger t_login
   after LOGON ON DATABASE
declare

begin
  -- 保存用户登录信息
  insert into loggin_event(username, logintime)
  values (USER, sysdate);
  commit;
end t_login;

-- 记录创建表的信息
drop table test_create_log;
create table test_create_log(
     event  varchar2(50),
     type varchar2(50),
     name varchar2(50),
     owner varchar2(50),
     createtime date  default sysdate   
);

-- 创建表的时候记录
create or replace trigger t_create
   after create on database  
declare
   v_event varchar2(50);
   v_type varchar2(50);
   v_name varchar2(50);
   v_owner varchar2(50);
begin
  -- 读取DDL事件属性
   v_event := sysevent; --事件
   v_type := dictionary_obj_type; -- 对象类型，如表
   v_name := dictionary_obj_name; -- 对象名称；如表没
   v_owner := dictionary_obj_owner; -- 拥有者
   insert into test_create_log values(v_event, v_type, v_name, v_owner, sysdate);
end t_create;   
   
select * from test_create_log;
drop table t_1;
create table t_1(id number);
select * from test_create_log;