oracle数据库sql语句的触发器实例讲解，oracle实例讲解
oracle数据库sql语句的触发器实例讲解
/**-----------------------
*语句级触发器
*/
--创建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;