触发器
功能特点:
为了确保数据库满足特定的商业规则或企业逻辑,可以使用约束,触发器,子程序(过程和函数) ;
触发器可以用于实现数据操作的 安全保护,数据审计,数据完整性,参照完整性和数据复制功能;
语句级触发器:
模板:
create or replace trigger trig_name
before | after
insert | or delete | or update on table_name
begin
pl/sql_block | call procedure_name
end;
示例:
create or replace trigger my_trigger before
insert or delete or update on emp
begin
if ta_char(sysdate,'day') in ('星期六','星期日')
or (to_char(sysdate, 'HH24') not between 8 and 18 ) then
rasie_application_error(20001,'不是上班时间');
end if;
end;
行级触发器:
模板:
create or replace trigger my_trig
before | after
insert | delete | update of 列名1, 列名2...
or insert |delete | update of 列名1, 列名2...
on table_name for each row when(trig_conditioin)
begin
pl/sql_block | call procedure_name
end;
示例:
create or replace trigger my_trigger2 before
update of sal,comm or delete on emp for each row when(old.job='SALESMAN')
begin
case
when updating('sal') then
if :new.sal < :old.sal then
raise_application_error(20002,'工资只能提高,不能减少');
end if;
when updating('comm') then
if :new.comm < :old.comm
raise_application_error(20003,'销售人员的补助只能涨,不能降');
when deleting then
raise_application_error(20004, '不能删除emp表中销售人员的记录');
end case;
end;