Question

Oracle数据库！！！行级触发器！！！:new:old怎么用？？？？谢谢！！

Answer 1

CREATE OR REPLACE TRIGGER IUD_BBBB
BEFORE insert or update or delete on EM_AAAA
for each row

declare
-- local variables here
v_count number;
begin

If Inserting Then

select count(*)
into v_count
from em_outlive
where fact_no = :new.fact_no
and pnl_no = :new.pnl_no
and ((nvl(:new.if_flag,'0') = '1' and nvl(in_date,'0') = '0') or
(nvl(:new.if_flag,'0') = '1' and nvl(in_date,'0') <> '0' and in_date >= :new.date_start) or
(nvl(:new.if_flag,'0') = '0' and nvl(in_date,'0') = '0' and out_date <= :new.date_end ) or
(nvl(:new.if_flag,'0') = '0' and nvl(in_date,'0') <> '0' and in_date >= :new.date_start and out_date <= :new.date_end));

if v_count > 0 then
RAISE_APPLICATION_ERROR(-20001,'此员工在日期：'||:new.date_start||'之后已经登记外宿，不可以在厂里登记住宿！');
ROLLBACK;
RETURN ;
end if;

if nvl(:new.if_flag,'0') = '1' then
update em_bed
set use = '1'
where fact_no = :new.fact_no
and build_no = :new.build_no
and floor_no = :new.floor_no
and room_no = :new.room_no
and bed_no = :new.bed_no;

elsif nvl(:new.if_flag,'0') = '0' then
update em_bed
set use = '0'
where fact_no = :new.fact_no
and build_no = :new.build_no
and floor_no = :new.floor_no
and room_no = :new.room_no
and bed_no = :new.bed_no;

end if;

end if;

if Updating then

select count(*)
into v_count
from em_outlive
where fact_no = :new.fact_no
and pnl_no = :new.pnl_no
and ((nvl(:new.if_flag,'0') = '1' and nvl(in_date,'0') = '0') or
(nvl(:new.if_flag,'0') = '1' and nvl(in_date,'0') <> '0' and in_date >= :new.date_start) or
(nvl(:new.if_flag,'0') = '0' and nvl(in_date,'0') = '0' and out_date <= :new.date_end ) or
(nvl(:new.if_flag,'0') = '0' and nvl(in_date,'0') <> '0' and in_date >= :new.date_start and out_date <= :new.date_end));

if v_count > 0 then
RAISE_APPLICATION_ERROR(-20001,'此员工在日期：'||:new.date_start||'之后已经登记外宿，不可以在厂里登记住宿！');
ROLLBACK;
RETURN ;
end if;

if nvl(:new.if_flag,'0') = '1' and nvl(:old.if_flag,'0') = '0' then
update em_bed
set use = '1'
where fact_no = :new.fact_no
and build_no = :new.build_no
and floor_no = :new.floor_no
and room_no = :new.room_no
and bed_no = :new.bed_no;

elsif nvl(:new.if_flag,'0') = '0' and nvl(:old.if_flag,'0') = '1'then
update em_bed
set use = '0'
where fact_no = :new.fact_no
and build_no = :new.build_no
and floor_no = :new.floor_no
and room_no = :new.room_no
and bed_no = :new.bed_no;
end if;
end if;

if Deleting then
if nvl(:old.if_flag,'0') = '1' then
update em_bed
set use = '0'
where fact_no = :old.fact_no
and build_no = :old.build_no
and floor_no = :old.floor_no
and room_no = :old.room_no
and bed_no = :old.bed_no;

end if;
end if;

end IUD_EM_BEDPNL;

Answer 2

:new跟:old都是一条记录，对于插入炒作前者有效，对于删除后者有效，对于更新两者都有效

追问

能举个例子么？？谢谢！