问题来源:
实际的项目中,有一张用户基本信息表sd2_userinfo,当用户重新注册时,要删除原来的用户信息记录,同时要删除该用户相关的其他表的信息。
表说明:
sd2_userinfo 用户基本信息表(id number 主键,troopid为该用户的战队ID,外键为sd2_troop.troopid)
sd2_trade 用户交易表
>desc sd2_trade
Name Null? Type
TRADEID NOT NULL NUMBER --交易ID号
GOODSID NUMBER --交易的物品ID号
OWNER NUMBER --物品的主人,外键为sd2_userinfo.id
PRICE NUMBER --交易的价格
sd2_troop 用户战队信息
>desc sd2_troop
Name Null? Type
TROOPID NOT NULL NUMBER --战队ID号
TROOPNAME VARCHAR2(100) --战队名称
LEADER NUMBER --队长,外键为sd2_userinfo.id
NUMBERS NUMBER --成员数
CREATETIME VARCHAR2(15) --战队创建时间
PK_WIN NUMBER --战队战斗胜利次数
PK_LOST NUMBER --战队战斗失败次数
MEMBER VARCHAR2(20) --成员,格式为sd2_userinfo.id:sd2_userinfo.id:
DESCRIPTION VARCHAR2(200) --战队描述
问题描述:
当删除sd2_userinfo中的一条记录时,相应的操作有,对表sd2_trade是delete from sd2_trade where owner=id,对sd2_troop分为三种情况
1、当此人为队长时,删除该战队,并将战队中的其他成员的troopid置为0
2、当此人为非队长,且该队成员数为2时,删除该战队,并将战队的队长的troopid置为0
3、当此人为非队长,且该队成员数大于2时,将该战队的numbers-1,member中踢除“该队员的id:”
具体的代码:
create global temporary table sd2_userinfo_temp --创建临时表
(
id number
) on commit delete rows;
create or replace trigger tri_sd2userinfo_adr --创建行级触发器
after delete on sd2_userinfo
referencing old as old new as new
for each row
begin
insert into sd2_userinfo_temp values(:old.id);
end;
create or replace trigger tri_sd2userinfo_ads --创建语句级触发器
after delete on sd2_userinfo
declare
num number;
mem varchar2(20);
lea number;
userid number;
begin
select id into userid from sd2_userinfo_temp;
delete from sd2_trade where sd2_trade.owner=userid; --删除他的交易记录
select sd2_troop.leader,sd2_troop.numbers,sd2_troop.member into lea,num,mem from sd2_troop where sd2_troop.member like concat(userid,':%') or sd2_troop.member like concat(concat('%:',userid),':%');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN;
if(lea=userid) then --当该用户为某队的队长时
delete from sd2_troop where sd2_troop.leader=lea; --删除该战队
update sd2_userinfo set sd2_userinfo.troopid=0 where mem like concat(sd2_userinfo.id,':%') or mem like concat(concat('%:',userid),':%');
elsif(num=2) then --当用户为队员,且该队的成员数为2时
delete from sd2_troop where sd2_troop.leader=lea; --删除该战队
update sd2_userinfo set sd2_userinfo.troopid=0 where sd2_userinfo.id=lea; --将该队的队长的troopid置为0
elsif(num>2) then --当用户为队员,且该队的成员数大于2时
update sd2_troop set sd2_troop.numbers=num-1,sd2_troop.member=replace(mem,concat(userid,':'),'') where sd2_troop.leader=lea;
end if;
end;