Oracle 触发器使用

  create tablespace ts1
  datafile 'D:\ts1.dbf'
  size 50m
  autoextend on 
  next 20m maxsize 2048m;
  
  create user student identified by student default tablespace ts1;
  
  grant connect , resource to student;
  grant create view to student;
  create table stuInfo
  (
    stuId int primary key,
    name varchar(10) not null,
    age int check(age>0 and age<100),
    sex varchar(5) default '男',
    telPhone int,
    createTime date default sysdate
  );
  
  create table stuScore
  (
    scoreId int primary key,
    stu_id int references stuInfo(stuId) not null,
    subject varchar(10),
    score float check(score>=0 and score<=100)
  )
  
  
  insert into stuInfo (stuId,name,age) values (sq_stuInfo.Nextval,'张三',20);
  insert into stuInfo (stuId,name,age) values (sq_stuInfo.Nextval,'李四',25);
  insert into stuInfo (stuId,name,age) values (sq_stuInfo.Nextval,'王五',22);
  insert into stuInfo (stuId,name,age) values (sq_stuInfo.Nextval,'赵柳',27);
  insert into stuInfo (stuId,name,age) values (sq_stuInfo.Nextval,'钱琪',30);
  insert into stuInfo (stuId,name,age) values (sq_stuInfo.Nextval,'啦啦啦',40);
  insert into stuInfo (stuId,name,age) values (sq_stuInfo.Nextval,'哒哒哒',30);
  insert into stuInfo (stuId,name,age) values (sq_stuInfo.Nextval,'张大',20);
  insert into stuInfo (stuId,name,age) values (sq_stuInfo.Nextval,'张二',10);
  insert into stuInfo (stuId,name,age) values (sq_stuInfo.Nextval,'王小二',29);
commit;
select * from stuInfo;
select * from stuScore;

insert into stuScore (scoreId,stu_id,subject,score) values (sq_score.nextval,1,'语文',80);
insert into stuScore (scoreId,stu_id,subject,score) values (sq_score.nextval,2,'语文',60);
insert into stuScore (scoreId,stu_id,subject,score) values (sq_score.nextval,3,'语文',85);
insert into stuScore (scoreId,stu_id,subject,score) values (sq_score.nextval,4,'语文',89);
insert into stuScore (scoreId,stu_id,subject,score) values (sq_score.nextval,5,'语文',50);
insert into stuScore (scoreId,stu_id,subject,score) values (sq_score.nextval,6,'语文',100);
insert into stuScore (scoreId,stu_id,subject,score) values (sq_score.nextval,7,'语文',99);
insert into stuScore (scoreId,stu_id,subject,score) values (sq_score.nextval,8,'语文',88);
insert into stuScore (scoreId,stu_id,subject,score) values (sq_score.nextval,9,'语文',74);
insert into stuScore (scoreId,stu_id,subject,score) values (sq_score.nextval,10,'语文',66);
commit;
select * from stuScore;

------------1 before 行级
create or replace trigger tig1
   before insert on stuInfo
   for each row
begin
   if :new.stuid>100 then
   raise_application_error(-20001,'超出范围');    
   end if;
end;       


  insert into stuInfo (stuId,name,age) values (101,'王小二',29);
------------  2 从表删除
  select * from stuInfo;
  create or replace trigger tig3
  before insert or delete or update on stuinfo  
 
  -----  before 语句级
  declare
  -- local variables here
  begin
    if user not in ('STUDENT') then
    raise_application_error(-20001,'没权限修改');
    end if;
  end tig3;
  delete from student.stuInfo where stuid=3;
  
  commit;
----------------  4 instead of
  
  create view view1 as
  select stuInfo.stuid,stuInfo.name,stuScore.subject,stuScore.score from stuInfo inner join stuScore on stuInfo.stuid=stuScore.stu_id;
  
  select * from view1;
  
  create or replace trigger tig4
  instead of delete on view1  
  for each row
  declare
  -- local variables here
  begin
    delete from stuscore where stu_id=:old.stuid;
    delete from stuInfo where stuid =:old.stuid;
    Dbms_Output.put_line('删除成功');
  end tig4;

  delete  from stuInfo where stuid=10;
  select * from stuInfo;
  select *from stuScore;
  
  ---------------after 行级
  create or replace trigger tig7
  after insert or update or delete on stuscore  
  for each row
  declare
  -- local variables here
  begin
    Dbms_Output.put_line('修改数据成功');
  end tig7;     
   
  update stuScore set score = 80 where subject='语文';
  commit;

  
  --------------after 语句级
  
  create or replace trigger tig6
  after insert or update or delete on stuscore  
  
  declare
  
  begin
    Dbms_Output.put_line('数据修改成功');
  end tig6;
  
insert into stuScore (scoreId,stu_id,subject,score) values (sq_score.nextval,21,'语文',80);
insert into stuScore (scoreId,stu_id,subject,score) values (sq_score.nextval,22,'语文',60);
insert into stuScore (scoreId,stu_id,subject,score) values (sq_score.nextval,23,'语文',85);
insert into stuScore (scoreId,stu_id,subject,score) values (sq_score.nextval,24,'语文',89);
insert into stuScore (scoreId,stu_id,subject,score) values (sq_score.nextval,25,'语文',50);

update stuScore set score = 80 where subject='语文';

commit;
delete from stuScore;
select *from stuScore;