20210813触发器

--创建触发器，在删除学生表学生记录之后触发
create or replace trigger student_trigger
after--后置触发器
delete--删除
on student1 --作用的表名
for each row --行级触发器，影响一行触发器，如果不写，执行一条语句不管影响多少行只触发一次
--pl/sql语句块
begin
  --old指的是旧的记录行对象，获取该行的所有字段值，new指的是新的记录行对象
  dbms_output.put_line('学生记录'||:old.id||'被删除');
end;
select * from student1 order by id;

delete from student1 where id <=5;
commit;

--创建触发器，对学生记录新增，修改，删除操作之后进行触发
create or replace trigger stu_trigger
after 
insert or update or delete
on student1
for each row 
  
begin
  --新增操作
  if inserting then
    dbms_output.put_line('学生记录【'||:new.name||'】新增成功');
  
  --修改操作
  elsif updating then
  dbms_output.put_line('学生记录【'||:old.id||'】密码由【'||:old.password||'【改成新密码【'||:new.password||'】');
  --删除擦操作
  elsif deleting then
  dbms_output.put_line('学生记录【'||:old.id||'】删除成功');
  end if;
end;

insert into student1 (id,name,username,password,sex,age,birthday,create_time)
values(1,'李四'，'lisi','123456','1','20',to_date('2001-03-10','yyyy-MM-dd'),sysdate);
commit;

update student1 set password ='666666' where id in(1,6);
commit;

delete from student where id<=10;
commit;

select * from student1;


--创建触发器，只能在12点~13点之间新增、修改、删除student表数据
create or replace trigger stu_trigger1
before
insert or update or delete
on student1
for each row
  
begin
  if to_char(sysdate,'hh24')!=12 then
    raise_application_error(-20001,'学生记录只能在12点~13点之间进行增删改操作');
  end if;
end;

select to_char(sysdate,'hh24') from dual;

select * from student1;
--禁用触发器
alter trigger stu_trigger1 disable;
--启动触发器
alter trigger stu_trigger1 enable;
--删除触发器
drop trigger stu_trigger1;

--创建视图
create or replace view view_student1 as
select id,name,username,password from student1 order by id;
---查询视图
select * from view_student;
--修改表数据
update student1 set password ='666666' where id in (1,2);
commit;

--修改视图数据，实则是修改表的数据
update view_student set password ='123456' where id in (1,2);
commit

select * from view_emp;

update view_emp e set e.dept_name = 'ITS' where e.id = 103;
commit;

--创建触发器instead of 对视图数据进行修改，针对包含来自多个
create or replace trigger view_emp_trigger
instead of--视图触发器，只作用在视图上
insert or  update or delete 
on view_emp --视图名称，包含来自多个表的字段
for each row
begin
  if inserting then
    dbms_output.put_line('新增');
  
  elsif updating then
      update departments d set d.department_name = :new.dept_name where d.department_id=(select e.department_id from employees e where e.department_id = :old.id);
     dbms_output.put_line('删除操作');
  elsif deleting then
  dbms_output.put_line('新增');
  end if;
end;
 
--模式触发器，用户级别，主要用在ddl语句中，创建，修改，删除对象时触发

create or replace trigger obj_trigger
after --后置对象
create or alter or drop --删除对象
on schema--作用在模式上，当前用户
begin
  dbms_output.put_line('当前用户;'||ora_dict_obj_owner||'对象名称：'||ORA_DICT_OBJ_NAME||'对象类型：'||ORA_DICT_OBJ_TYPE||',删除时间：'||to_char(sysdate,'yyyy-MM-dd'));
end;

drop table student_bak20210806;

create table test(
id number,
name varchar2(10));

create index idx_test_name on test(name);
 drop index idx_test_name;

--使用触发器，给员工涨工资，工资的值必须大于旧的值，否则提示错误  前置触发器
create or replace trigger emp_sal_trigger
before 
update
on employees
for each row
begin
  if :new.salary < :old.salary then
    raise_application_error(-20001,'工资的值必须大于旧的值');
  end if;
end;
update employees e set e.salary = 50000 where e.employee_id = 105;

select * from employees;



--使用触发器，修改学生性别，必须是1或者是0，否则提示错误  前置触发器
create or replace trigger student_trigger
before
update 
on student
for each row
begin
  if :new.sex not in(0,1) then
    raise_application_error(-20001,'修改学生性别，必须是1或者是0');
  end if;
end;

--测试
select *from student;
update student s set s.sex =0 where s.id =1;

--创建表test和备份test_bak,当对表test 新增，修改，删除数据时，同步更新test_bak表，使用触发器现实
create table test(
id number primary key,
name varchar2(10),
sex number,
age number,
create_time date);

--插入数据
declare
  birthday date;
begin
  for i  in 1..50
    loop
      birthday :=TO_DATE (TRUNC (DBMS_RANDOM.VALUE (TO_NUMBER (TO_CHAR (add_months(sysdate,-20*12), 'J')),TO_NUMBER (TO_CHAR (add_months(sysdate,-30*12), 'J')))),'J');
      insert into test(Id,Name,Sex,Age,Create_Time) 
values(i,'张三'||i,trunc(dbms_random.value(0,2)), ceil(months_between(sysdate,birthday)/12),sysdate);
    end loop;
    commit;
end;

select * from test;
select * from test_bak;
create table test_bak as select * from test;

drop table test;


--创建触发器instead of 对视图数据进行修改，针对包含来自多个
create or replace trigger view_emp_trigger
instead of--视图触发器，只作用在视图上
insert or  update or delete 
on view_emp --视图名称，包含来自多个表的字段
for each row
begin
  if inserting then
    dbms_output.put_line('新增');
  
  elsif updating then
      update departments d set d.department_name = :new.dept_name where d.department_id=(select e.department_id from employees e where e.department_id = :old.id);
     dbms_output.put_line('删除操作');
  elsif deleting then
  dbms_output.put_line('新增');
  end if;
end;
--创建表test和备份test_bak,当对表test 新增，修改，删除数据时，同步更新test_bak表，使用触发器现实
--创建触发器
create or replace trigger test_trigger
after
insert or update or delete
on test
for each row
begin
  
  if inserting then
    insert into test_bak  (id,name,sex,age,create_time)values(:new.id,:new.name,:new.sex,:new.age,:new.create_time);
    dbms_output.put_line('新增成功');
  
  elsif updating then
    update test_bak t set t.id =:new.id,t.name=:new.name,t.sex=:new.sex,t.age=:new.age,t.create_time=:new.create_time where t.id = :new.id;
    dbms_output.put_line('修改成功');
  elsif deleting then
  delete test_bak t where t.id = :old.id;
  dbms_output.put_line('删除成功');
  end if;
  end;

--测试
select * from test;
select * from test_bak;
delete from test where id=51;
insert into test(id,name,sex,age,create_time) values(52,'王五',1,20,sysdate);
commit;

update test t set t.sex =0 where t.id=3;
commit;

delete from test where id = 1;
commit;