触发器（二）

----实例--------------
--要求：当用户向pub_users表添加和删除数据时，将pub_users中的name字段和系统时间(yyyymmdd hh24:mi:ss)
--插入到表pub_logs的name和create_time字段


--创建表
create table pub_users(
   user_Id number(4) primary key,
   name varchar2(8),
   pwd varchar2(20)
);

--添加数据
insert into pub_users values (1,'sss','sss');
insert into pub_users values (2,'ttt','ttt');
  commit;
 
create table pub_logs(
  username varchar2(8),
  create_time varchar2(30)
);



--创建触发器
create or replace trigger crud_user_trigger
after insert or update or delete on pub_users  --触发语句
for each row
begin
  insert into pub_logs values(:old.name,to_char(sysdate,'yyyymmdd hh24:mi:ss'));
end;

--测试
update  pub_users set pwd=123456  where user_id=1;

commit;







--------实例1-----------------
--创建触发器，当用户对test表执行DML语句时，将相关信息记录到日志表test_log

--创建测试表
CREATE TABLE test
(
t_id   NUMBER(4),
t_name VARCHAR2(20),
t_age  NUMBER(2),
t_sex  CHAR
);
--创建记录测试表
CREATE TABLE test_log
(
l_user   VARCHAR2(15),
l_type   VARCHAR2(15),
l_date   VARCHAR2(30)
);
--创建触发器
CREATE OR REPLACE TRIGGER test_trigger
AFTER DELETE OR INSERT OR UPDATE ON test
DECLARE
v_type test_log.l_type%TYPE;
BEGIN
IF INSERTING THEN  --INSERT触发
  v_type := 'INSERT';
  DBMS_OUTPUT.PUT_LINE('记录已经成功插入，并已记录到日志');
ELSIF UPDATING THEN  --UPDATE触发
  v_type := 'UPDATE';
  DBMS_OUTPUT.PUT_LINE('记录已经成功更新，并已记录到日志');
ELSIF DELETING THEN
  v_type := 'DELETE';
  DBMS_OUTPUT.PUT_LINE('记录已经成功删除，并已记录到日志');
END IF;
INSERT INTO test_log VALUES(user,v_type,
        TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss'));
END;
/
--下面我们来分别执行DML语句
INSERT INTO test VALUES(101,'zhao',22,'M');
UPDATE test SET t_age = 30 WHERE t_id = 101;
DELETE test WHERE t_id = 101;
--然后查看效果
SELECT * FROM test;
SELECT * FROM test_log;


--实例2------------------------
  --创建触发器，它将每个部门的总人数和总工资同步更新到一个表dept_sal中（注：每当用户对emp表，进行增删改操作，都重新做统计）
 
  --创建映射表
  CREATE TABLE dept_sal
  AS
  SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal FROM emp GROUP BY deptno;
 
  --创建触发器
  create or replace trigger emp_info
  after insert or update or delete on emp
  declare CURSOR cur_emp is  --声明游标
    select deptno,count(empno) as total_emp,sum(sal) as total_sal from emp group by deptno;
begin
   delete dept_sal;--触发时首先删除映射表信息
   for v_emp in cur_emp loop
    --插入数据
    insert into dept_sal   VALUES(v_emp.deptno,v_emp.total_emp,v_emp.total_sal);
   end loop;
  end;
 
  --对emp表进行DML操作
insert  into emp(empno,deptno,sal) VALUES(1111,10,10000);
--DELETE emp WHERE empno=1111;
commit;

--实例3-------
--创建触发器，它记录表的删除数据

--创建表
CREATE TABLE employee(
  id   VARCHAR2(4)  NOT NULL,
  name VARCHAR2(15) NOT NULL,
  age  NUMBER(2)    NOT NULL,
  sex  CHAR         NOT NULL
  );
 
  --插入数据
  INSERT INTO employee VALUES('e101','zhao',23,'M');
  INSERT INTO employee VALUES('e102','jian',21,'F');
  commit;
 
  --创建记录表
  CREATE TABLE old_employeeeeee AS SELECT * FROM employee;

  --创建触发器
  create or replace trigger  tig_old_emp
  after delete on employee
  for each row
  begin
    insert into old_employee values(:old.id,:old.name,:old.age,:old.sex);--:old代表旧值
  end ;
 
 
  --下面进行测试
  DELETE employee;
  SELECT * FROM old_employee;


--实例4------------------------
--创建触发器，利用视图插入数据(注：在试图中插入一条语句，那么相关的2个表中都被插入了数据)

--创建表
CREATE TABLE tab1 (
tid NUMBER(4) PRIMARY KEY,
tname VARCHAR2(20),
tage NUMBER(2)
);

CREATE TABLE tab2 (
tid NUMBER(4),
ttel VARCHAR2(15),
tadr VARCHAR2(30)
);

--插入数据
INSERT INTO tab1 VALUES(101,'zhao',22);
INSERT INTO tab1 VALUES(102,'yang',20);
INSERT INTO tab2 VALUES(101,'13761512841','AnHuiSuZhou');
INSERT INTO tab2 VALUES(102,'13563258514','AnHuiSuZhou');

--创建视图连接两张表
create view tab_view as
select tab1.tid,tname,ttel,tadr from tab1,tab2
where tab1.tid=tab2.tid;

--创建触发器
create or replace trigger tab_trigger
instead of insert on tab_view
  begin
    insert into tab1(tid,tname)values (:new.tid,:new.tname);
    insert into tab2(ttel,tadr)values(:new.ttel,:new.tadr);
  end;
 
  --现在就可以利用视图插入数据
  INSERT INTO tab_view VALUES(105,'zhaoyang','13886681288','beijing');
  commit;
  --查看效果
  SELECT * FROM tab_view;


--创建触发器，比较emp表中更新的工资
CREATE OR REPLACE TRIGGER sal_empBEFORE
before UPDATE ON emp
FOR EACH ROW
BEGIN
  IFld.sal>:new.sal then
   DBMS_OUTPUT.PUT_LINE('工资减少');
   ELSIF :OLD.sal < :NEW.sal THEN
    DBMS_OUTPUT.PUT_LINE('工资增加');
   ELSE  DBMS_OUTPUT.PUT_LINE('工资未作任何变动');
  END IF;
DBMS_OUTPUT.PUT_LINE('更新前工资 ：' || :OLD.sal);
DBMS_OUTPUT.PUT_LINE('更新后工资 ：' || :NEW.sal);
END;

--执行UPDATE查看效果
UPDATE emp SET sal = 5000 WHERE empno = '7788';
commit;

--创建触发器，将操作CREATE、DROP存储在log_info表

--创建表
CREATE TABLE log_info(
  manager_user VARCHAR2(15),
  manager_date VARCHAR2(15),
  manager_type VARCHAR2(15),
  obj_name     VARCHAR2(15),
  obj_type     VARCHAR2(15)
);

--创建触发器
create or replace trigger trig_log_info
after create or drop on schema
begin
  insert into log_info values(
   user,
   sysdate,
   sys.dictionary_obj_name,
   sys.dictionary_obj_owner,
   sys.dictionary_obj_type
  );
end;

--查看效果
CREATE TABLE a(id NUMBER);
CREATE TYPE aa AS OBJECT(id NUMBER);
DROP TABLE a;
DROP TYPE aa;


--查看效果
SELECT * FROM log_info;