欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-03 20:40:01
oraclesql语句的触发器实例讲解 /**----------------------- *语句级触发器 */ --创建emp副本emp1 create table emp1 as select...

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;