oracle 触发器

[xm_bluesky]

CREATE TABLE WhiteList ( --白名单表
unitID VARCHAR2(50) PRIMARY KEY, --单位ID
email VARCHAR2(100) NOT NULL , --邮箱
unitName VARCHAR2(100) --单位名称
);
CREATE TABLE BlackList ( --黑名单表
unitID VARCHAR2(50) PRIMARY KEY,
email VARCHAR2(100) NOT NULL ,
unitName VARCHAR2(100)
);

insert into WhiteList value('421023','[e-mail]','aaa');

insert into BlackList value('421000','[e-mail]','bbb');


当我向BlackList表中insert into BlackList value('421023','[e-mail]','aaa');时那么就他是把WhiteList表中email为刚insert的'421023'记录删除
同理向白名单表中添加了黑名单的项 , 则把黑名单中相应的记录就删除
写实现这样功能的触发器

[shenshanguo]

删除blacklist
blacklist create or replace trigger INSTEAD_WHITE_BLACK
before insert on whitelist
for each row
declare
num number;
begin
IF INSERTING then
select count(*) into num from blacklist b where :new.email=b.email;
if num>0 then
delete from blacklist b where :new.email=b.email;
end if;
end if;
end INSTEAD_WHITE_BLACK;
删除whitelist
blacklist create or replace trigger INSTEAD_BLACK_WHITE
before insert on blacklist
for each row
declare
num number;
begin
IF INSERTING then
select count(*) into num from whitelist b where :new.email=b.email;
if num>0 then
delete from whitelist b where :new.email=b.email;
end if;
end if;
end INSTEAD_BLACK_WHITE;

[xm_bluesky]

还是实现不了

[stephenxu111]

这个应该可以实现啊。
不过为什么要判断INSERTING呢？
删除blacklist
create or replace trigger INSTEAD_WHITE_BLACK
before insert on whitelist
for each row
declare
num number;
begin

select count(*) into num from blacklist b where :new.email=b.email;
if num>0 then
delete from blacklist b where :new.email=b.email;
end if;

end INSTEAD_WHITE_BLACK;
删除whitelist
create or replace trigger INSTEAD_BLACK_WHITE
before insert on blacklist
for each row
declare
num number;
begin

select count(*) into num from whitelist b where :new.email=b.email;
if num>0 then
delete from whitelist b where :new.email=b.email;
end if;

end INSTEAD_BLACK_WHITE;