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;