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','[email protected]','aaa');insert into BlackList value('421000','[email protected]','bbb');
当我向BlackList表中insert into BlackList value('421023','[email protected]','aaa');时那么就他是把WhiteList表中email为刚insert的'421023'记录删除
同理向白名单表中添加了黑名单的项 , 则把黑名单中相应的记录就删除
写实现这样功能的触发器
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','[email protected]','aaa');insert into BlackList value('421000','[email protected]','bbb');
当我向BlackList表中insert into BlackList value('421023','[email protected]','aaa');时那么就他是把WhiteList表中email为刚insert的'421023'记录删除
同理向白名单表中添加了黑名单的项 , 则把黑名单中相应的记录就删除
写实现这样功能的触发器
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;
不过为什么要判断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;