级连删除触发器

(二个有外键关系的表,当删除主表一条记录时,级连删除子表的相关记录).

如果仅仅想实现级联删除的话。可能创建创建带级联删除的外键。这样的话，会好很多。如这样：alter table proout add constraint fk2 foreign key(cusno)references customer (cusno) on delete cascade你也可以将delete换成update以实现级联修改。当然，如果你想使用触发器的话，也可以这样：if OBJECT_ID('Tri_delete')>0drop trigger Tri_deletegocreate trigger Tri_deletefor deleteasbegin--批量删除 BEGIN     set nocount on        delete from 子表                where 子表.id in (select id from deleted)set nocount off END   if(@@error<>0)  begin  rollback transaction  raiserror('触发器Tri_delete删除数据操作异常!',16,1)  endend go

== 摘录自百度问答

写一个触发器，当主表A记录被删除时，同步删除子表B,C,D的记录：
create trigger trg_表A_deleteon 表Aafter deleteasbeingdelete from 表B,deleted where 表B.id=deleted.iddelete from 表c,deleted where 表c.id=deleted.iddelete from 表d,deleted where 表d.id=deleted.idend

表示：这个比较容易明白

oracle  sql:

create or replace trigger delete_abbefore delete on afor each row    delete from b      where b.id=:old.id;end delete_ab;

Oracle中主从多表删除数据时：

1.用触发器；

2.建表时加关键字。比如B表某列关联A表主键列，则：
create table b (col number references a(col) on delete cascade);
后面的C表D表类似处理。

级联删除既能用外键on delete cascade, 也能用触发器
但是触发器消耗资源较多, 而且有经验的DBA可能会知道,解发器会引起许多麻烦, 不建议使用

外键多级级联删除测试：

SQL> create table t1  2  (  3  id varchar2(10),  4  name varchar2(10)  5  ); Table created SQL> create table t2  2  (  3  id varchar2(10),  4  pid varchar2(10),  5  name varchar2(10)  6  ); Table created SQL> SQL> create table t3  2  (  3  id varchar2(10),  4  pid varchar2(10),  5  name varchar2(10)  6  ); Table createdSQL> alter table t1  2     add constraint pk_t1 primary key (id); Table alteredSQL> SQL> alter table t2  2     add constraint pk_t2 primary key (id); Table altered SQL> SQL> alter table t3  2     add constraint pk_t3 primary key (id); Table alteredSQL> alter table t2 add constraint FK_t1_t2 foreign key (pid) references t1(id) on delete cascade not deferrable; Table alteredSQL> alter table t3 add constraint FK_t2_t3 foreign key (pid) references t2(id) on delete cascade not deferrable; Table alteredSQL> insert into t1 values('1','aaa'); 1 row inserted SQL> insert into t1 values('2','bbb'); 1 row inserted SQL> insert into t2 values ('1','1','mmm'); 1 row inserted SQL> insert into t2 values ('2','1','nnn'); 1 row inserted SQL> insert into t3 values ('1','1','xxx'); 1 row inserted SQL> commit; Commit complete SQL> select * from t1; ID         NAME---------- ----------1          aaa2          bbb SQL> select * from t2; ID         PID        NAME---------- ---------- ----------1          1          mmm2          1          nnn SQL> select * from t3; ID         PID        NAME---------- ---------- ----------1          1          xxxSQL> delete t1 where id = '1'; 1 row deleted SQL> commit; Commit complete SQL> select * from t1; ID         NAME---------- ----------2          bbb SQL> select * from t2; ID         PID        NAME---------- ---------- ---------- SQL> select * from t3; ID         PID        NAME---------- ---------- ---------- SQL> rollback; Rollback complete

Oracle 触发器 插入，更新，删除,数据同步，两表同步

建表：create table User_Info (   ID                   INTEGER                         not null,   UserName            VARCHAR(30)                     not null,   PassWord            VARCHAR(20)                     not null,   CreateDate          Date                            not null,   Status              INTEGER                         not null,   constraint PK_User_Info primary key (ID));create table User_Info_temp (   ID                   INTEGER                         not null,   UserName            VARCHAR(30)                     not null,   PassWord            VARCHAR(20)                     not null,   CreateDate          Date                            not null,   Status              INTEGER                         not null,   constraint PK_User_Info_temp primary key (ID));触发器写法：create or replace trigger UserToTemp after insert or update or deleteon user_info for each rowdeclare    integrity_error exception;    errno            integer;    errmsg           char(200);    dummy            integer;    found            boolean;    beginif inserting then    insert into User_info_temp(ID,UserName,PassWord,CreateDate,Status) values(:NEW.ID,:NEW.UserName,:NEW.PassWord,:new.CreateDate,:NEW.Status);elsif updating then     update User_info_temp set ID=:NEW.ID,UserName=:NEW.UserName,PassWord=:NEW.PassWord,Status=:NEW.Status where id=:OLD.id;elsif deleting then    delete from User_info_temp where id=:OLD.id;end if;exception    when integrity_error then       raise_application_error(errno, errmsg);end;测试数据：insert into user_info(ID,UserName,PassWord,CreateDate,Status)values(1,'xier','222',to_date('2008-10-11','yyyy-mm-dd'),1)update user_info u set u.status=3,u.username='xier' where u.id=1delete from user_info u where u.id=1