pl/sql 编程（十二）

• 级联修改触发器

create trigger mutli_tgr 
after update of goodsid on goods
for each row
begin
    update sa set goodsid = :new.goodsid where goodsid = :old.goodsid;
    dbms_output.put_line('数据已经级联修改……');
end;

• 语句级触发器

create trigger stat_tgr 
before delete on goods2
begin
       if to_char(sysdate,'dd')='20' then
          raise_application_error(-20000,'今天不允许删除数据！！');
       end if;
end;

• 设置触发器的执行顺序

create or replace trigger tri_follow1 
before insert on goods2
begin
       dbms_output.put_line('触发器tri_follow1……');
end;

create or replace trigger tri_follow2
before insert on goods2
follows tri_follow1
begin
        dbms_output.put_line('触发器tri_follow2……');
end;

• 替代触发器

create or replace trigger  tri_instead
instead of  insert on goods_view
begin
    insert into goods values(:new.goodsid,:new.goodsname,:new.remark);   
end;

• DDL触发器

create trigger tri_ddl 
before alter or drop or rename on schema
begin
     if sysevent = 'drop'then
        if dictionary_obj_name ='goods'then
           raise_application_error(-20002,'不允许删除表goods');
        end if;
     elsif sysevent = 'alter' then
           raise_application_error(-20002,'不允许修改表goods的结构');
     elsif sysevent = 'rename' then
           if dictionary_obj_name ='goods'then
              raise_application_error(-20002,'不允许修改表goods的名字');
           end if;
     end if;
        
end;

• 查看触发器

select object_name from user_objects where object_type ='TRIGGER';

select * from user_source where name ='TRI_DDL' order by line;

• 触发器重命名、使失效、使有效、删除

alter trigger TRI_DDL rename to TRI_DDL1;
alter trigger TRI_DDL disable;
alter trigger TRI_DDL enable;
drop trigger TRI_DDL;