级连删除触发器

最新推荐文章于 2024-08-03 23:10:45 发布

懒丹

最新推荐文章于 2024-08-03 23:10:45 发布

阅读量1.3k

点赞数 1

分类专栏：数据库文章标签： oracle plsql

数据库专栏收录该内容

15 篇文章 1 订阅

订阅专栏

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

如果仅仅想实现级联删除的话。可能创建创建带级联删除的外键。这样的话，会好很多。
如这样：
alter table proout 
add constraint fk2 foreign key(cusno)
references customer (cusno) 
on delete cascade
你也可以将delete换成update以实现级联修改。
当然，如果你想使用触发器的话，也可以这样：
if OBJECT_ID('Tri_delete')>0
drop trigger Tri_delete
go

create trigger Tri_delete
for delete
as
begin
--批量删除
 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)
  end
end 
go

== 摘录自百度问答

写一个触发器，当主表A记录被删除时，同步删除子表B,C,D的记录：
create trigger trg_表A_delete
on 表A
after delete
as
being
delete from 表B,deleted where 表B.id=deleted.id
delete from 表c,deleted where 表c.id=deleted.id
delete from 表d,deleted where 表d.id=deleted.id
end

表示：这个比较容易明白

oracle  sql:

create or replace trigger delete_ab
before delete on a
for 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 created

SQL> alter table t1
  2     add constraint pk_t1 primary key (id);
 
Table altered

SQL> 
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 altered

SQL> alter table t2 add constraint FK_t1_t2 foreign key (pid) references t1(id) on delete cascade not deferrable;
 
Table altered

SQL> alter table t3 add constraint FK_t2_t3 foreign key (pid) references t2(id) on delete cascade not deferrable;
 
Table altered

SQL> 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          aaa
2          bbb
 
SQL> select * from t2;
 
ID         PID        NAME
---------- ---------- ----------
1          1          mmm
2          1          nnn
 
SQL> select * from t3;
 
ID         PID        NAME
---------- ---------- ----------
1          1          xxx

SQL> 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 delete
on user_info for each row
declare
    integrity_error exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
    
begin
if 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=1


delete from user_info u where u.id=1