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
解决方案 »
- 求解,在package body中取得值。
- 怎么写一个shell文件,按照条件删除2个表的数据,还要兼顾2个表的数据的整合性, 删除用的存储过程已经写好
- 读取 blob 数据文件
- 把Excel数据导到Oracle如何实现加密//
- 如何对oracle查询进行某些CHAR字段进行排序呢?
- 怎样设置主键
- 求助:非强关联的SQL要怎么写?
- 临时表空间一直不释放空间,是否正常?
- 新手提问,送分!!请问我在WIN2K专业版下装ORCALE8I,可是总是SETUP以后,什么反映也没有。而且我在多台机子上都不行。可以肯定的是这些
- 9i数据插入11g报ora-00904标注符无效,急!!!
- 对于大量数据的存储,数据库的选择问题
- 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'
的意义是什么?