11.触发器
删除触发器 DROP TRIGGER (触发器名称);
alfet 修改之后
1.普通触发器(一般没用,没意义)
例:
create or replace trigger tri_aa
before delete on teachers
begin
raise_application_error(-20403,'敢删我表!'); //错误信息从负两万开始,-20000到-20500
end;
2.时间触发器(规定时间触发)
例:9:00-18:00 teachers5表操作
create or replace trigger tri_aa2
before update on teachers5
begin
if(to_char(sysdate,'HH24')not between 9 and 17) //取18会导致18:30也能操作
then
raise_application_error(-20444,'下班!');
end if;
end;
例:股票 9:00--11:30 13:00--15:00
create or replace trigger tri_aab
before update on teachers
begin
if(to_char(sysdate,'hh24mi') between 900 and 1130 or to_char(sysdate,'hh24mi') between 1300 and 1500)
then
dbms_output.put_line('修改成功');
else
raise_application_error(-20412,'不在服务时间范围内');
end if;
end;
10:00秒杀((sysdate,'HH24MISS'))
3.数据触发器(根据数据触发)
第一个例子:工资只能降低不能升
create or replace trigger tri_aa3
before update of wage on teachers5 for each row
begin
if(:new.wage> :old.wage) //:new代表新数据,Old旧数据
then
raise_application_error(-20222,'不厚道');
end if;
end tri_aa3;
第二个例子:工资无论加减不能超过20%
create or replace trigger tri_aa4
before update of wage on teachers
for each row
begin
if(abs(:new.wage-:old.wage)>0.2*:old.wage)
then
raise_application_error(-20223,'修改幅度过大);
end if;
end tri_aa4;
例:创建一个自增序列,当插入新数据的时候表id自动增长
创建一个序列create sequence s2
create or replace trigger tri_aa6
before insert on teachers for each row
begin
:new.Teacher_id:=s2.nextval;
end tri_aa6;
思考:
1.accp1
accp2
2.201814001
201814002
1,股价只能每天上涨10%
当量超过5000股延时五分钟
热备份触发器(如果表1修改,表2会进行备份记录)S2是序列,要根据表的内容进行备份
create or replace trigger tri_aa7
after update on teachers for each row
begin
insert into beifen values(s2.nextval,:new.name,:old.wage,:new.wage,sysdate);
end tri_aa7;
十六:异常
例1.除数为0
declare
a number:=6;
b number:=0;
c number:=0;
begin
c:=a/b;
Exception
when zero_DIVIDE THEN
DbMS_OUTPUT.PUT_LINE('除数为0');
when others then
DbMS_OUTPUT.PUT_LINE('其他异常');
end;
例2.空指针
declare
gongzi teachers.wage%type;
begin
select wage into gongzi from teachers where name='嘻嘻';
Exception
when No_DATA_found then //抓空指针
DBMS_OUTPUT.PUT_LINE('空指针');
when others then //抓其他异常
DBMS_OUTPUT.PUT_LINE('其他异常');
end;
十七:自定义异常
declare
e_error exception;
gongzi teachers.wage%type;
begin
select wage into gongzi from teachers where name='林飞';
if(gongzi<2000)
then
raise e_error;
end if;
Exception
when e_error then
DBMS_OUTPUT.PUT_LINE('系统异常');
end;