Oracle 10G 数据库系统学习笔记《触发器》2008-09-18 19:29事务操作命令

SQL> rollback

SQL> commit

.

行级触发器

SQL> create or replace trigger del_table1
after delete on table1
for each row
begin
delete from table2 where table1_id=:old.id;
end;
/

SQL> create or replace trigger insert_table1
after insert on table1
for each row
begin
insert into table2(id,name,table1_id) valures(myseq.nextval,'test',:new.id);
end;
/

SQL> create or replace trigger update_table1
after update on table1
for each row
begin
update table2 set table1_id=:new.id where table1_id=:old.id;
end;
/

SQL> create or replace trigger table1_delete
after delete on table1
for each row
begin
if :old.id='001' then   //不允许删除id为‘001’的记录
raise_application_error(-20000,'不允许删除！');   //错误号范围-29999至-20000
end if;
end;
/

.

SQL> create table mylog(
curr_user varchar2(100),
curr_date date,
act char(1));

语句级触发器

SQL> create or replace trigger dml_table1
after insert or delete or update on table1
begin
if inserting then
insert into mylog values(user,sysdate,'I');
elsif deleting then
insert into mylog values(user,sysdate,'D');
else
insert into mylog values(user,sysdate,'U');
end if;
end;
/

结合触发器利用序列实现表中整型字段的自增

SQL> create or replace trigger set_no
before insert on table1
for each row
declare
sn number(5);
begin
select myseq.nextval into sn from dual;
:new.id:=sn;
end;
/

替换触发器可解决视图的多表更新问题（替换触发器只能建在视图上）

SQL> create or replace trigger trigger_view_table1_table2
instead of insert on view_table1_table2
for each row
begin
insert into table1 values(:new.id, :new.name);
insert into table2 values(:new.tid, :new.tname, :new.id);
end;
/

SQL> insert into view_table1_table2 values(12,'t2',34,'t1'