SQL触发器代码:CREATE trigger tri_insert on dbo.phonedata
for update
as
declare @c int
begin
select @c=count(i.ani) from phonedata p,inserted i
where p.subbecalleds=i.subbecalleds and i.endtime <>' ' and i.userid is not null and i.state='y'
if @c > 0
begin
insert into callend
select i.* from phonedata p , inserted i
where p.ani=i.ani and p.starttime=i.starttime and i.endtime <>' ' and i.userid is not null and i.state='y'
delete phonedata
from phonedata a,
( select i.ani,i.starttime from phonedata p , inserted i
where p.ani=i.ani and p.starttime=i.starttime and i.endtime <>' ' and i.userid is not null and i.state='y'
) callend
where a.ani=callend.ani and a.starttime=callend.starttime
end
end
for update
as
declare @c int
begin
select @c=count(i.ani) from phonedata p,inserted i
where p.subbecalleds=i.subbecalleds and i.endtime <>' ' and i.userid is not null and i.state='y'
if @c > 0
begin
insert into callend
select i.* from phonedata p , inserted i
where p.ani=i.ani and p.starttime=i.starttime and i.endtime <>' ' and i.userid is not null and i.state='y'
delete phonedata
from phonedata a,
( select i.ani,i.starttime from phonedata p , inserted i
where p.ani=i.ani and p.starttime=i.starttime and i.endtime <>' ' and i.userid is not null and i.state='y'
) callend
where a.ani=callend.ani and a.starttime=callend.starttime
end
end
解决方案 »
- oracle11g 归档日志满了,无法启动!
- 汉化版sqldeveloper的快捷键设置
- procedure function 区别,如何在pl/sql developer中运行他们
- Oracle数据迁移问题,急,在线等...谢谢
- Oracle临时表问题
- 求教:用varchar(8)还是Number(8)作主键,效率如何?
- 才开始学Oracle问个弱智问题:在oracle中每添加一条记录,记录是不是一定都添加到表的最后一条的后面呀
- [马上结贴]请问如何知道一个表或View的创建的时间?谢谢。
- 非常简单的问题
- 高分咨询:如何用vc++实现从oracle里的数据转换为sql、access里的数据。(不够在加分)
- 对于大量数据的存储,数据库的选择问题
- Oracle 9.2.0.6下UTL_FILE文件读取超长问题
before update on phonedata
for each row
as
c integer;
begin
select nvl(count(:new.ani),0) into c
from phonedata p
where p.subbecalleds=:new.subbecalleds and :new.endtime <>' '
and :new.userid is not null and :new.state='y';
if c > 0 then
insert into callend
select :new.* from phonedata p
where p.ani=:new.ani and p.starttime=:new.starttime and :new.endtime <>' '
and :new.userid is not null and :new.state='y';
delete from phonedata a
where a.ani=:new.ani and a.starttime=:new.starttime and :new.endtime <>' '
and :new.userid is not null and :new.state='y'
and a.starttime=:new.starttime;
end if;
end tri_insert ;
/
after update on phonedata
for each row
declare
v_count number;
begin
v_count=0;
select count(:new.ani) into v_count from phonedata p
where p.subbecalleds=:new.subbecalleds and :new.endtime <>' ' and :new.userid is not null and :new.state='y';
if v_count > 0 then
insert into callend
select :new.* from phonedata p
where p.ani=:new.ani and p.starttime=:new.starttime and :new.endtime <>' ' and :new.userid is not null and :new.state='y'; delete from phonedata
where p.ani=:new.ani and p.starttime=:new.starttime and :new.endtime <>' ' and :new.userid is not null and :new.state='y';
end if;
exception
when NO_DATA_FOUND then raise_application_error(-20001,'failed operation!');
end tri_insert;
将:new分解为:new.ani,:new.starttime,:new.endtime,...试试
select @c=count(i.ani) from phonedata p,inserted i
where p.subbecalleds=i.subbecalleds and i.endtime <>' ' and i.userid is not null and i.state='y'
的意义是什么?