oracle数据库sql语句的触发器实例讲解

oraclesql语句的触发器实例讲解

/**-----------------------

*语句级触发器

*/

--创建emp副本emp1

create table emp1 as select * from emp where 1=2;

--插入触发器

create or replace trigger trg_emp1_insert_output_number

after insert on emp1

declare

num number(10);

begin

select count(*) into num from emp1;

dbms_output.put_line(num);

end;

/

--

insert into emp1 select * from emp where deptno=10;

insert into emp(empno,ename,sal) values(111,'sss', 5000);

--更新触发器

create or replace trigger trg_emp_update

after update on emp

declare

v_sal number;

begin

select avg(sal) into v_sal from emp;

dbms_output.put_line(v_sal);

end;

/

--

update emp set sal=sal+100;

--插入 或 更新 或 删除

create or replace trigger trg_emp1

after insert or update or delete on emp1

declare

num number(10);

v_sal number(7,2);

begin

if inserting then

select count(*) into num from emp1;

dbms_output.put_line('人数：'||num);

elsif updating then

select avg(sal) into v_sal from emp1;

dbms_output.put_line('平均工资：'||v_sal);

else

for i in (select deptno, count(*) num from emp1 group by deptno) loop

dbms_output.put_line(i.deptno||'人数:'||i.num);

end loop;

end if;

end;

/

--insert

insert into emp1 select * from emp where deptno=10;

--update

update emp1 set sal=sal+100;

--emp2

create table emp2 as select * from emp where 1=2;

--禁止非工作时间对emp2表进行操作

create or replace trigger trg_emp2

before insert or update or delete on emp2

begin

if to_char(sysdate, 'hh24:mi') not between

'08:00' and '18:00' or to_char(sysdate,

'dy','nls_date_language=american')

in('sat','sum')

then

raise_application_error(-20015,'只能在工作时间操作');

end if;

end;

/

--insert

insert into emp2 select * from emp where deptno=10;

--update

update emp2 set sal=sal+100;

--emp3

create table emp3 as select * from emp;

/**-----------------------

*行句级触发器

*/

create or replace trigger trg_emp3

after update of sal on emp3

for each row

begin

if updating then

dbms_output.put_line('更新编号：'||:new.empno||'更新工资'||:old.sal||'更新后工资'||:new.sal);

end if;

end;

/

--update

update emp3 set sal=sal+100 where empno=7934;