ORACLE 资料整理(三)触发器

最新推荐文章于 2025-08-16 09:37:50 发布

原创最新推荐文章于 2025-08-16 09:37:50 发布 · 136 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #Trigger

oracle 专栏收录该内容

3 篇文章

订阅专栏

本文深入探讨了数据库触发器的创建与应用，详细介绍了如何在Oracle数据库中使用触发器实现自动电话回拨系统，包括插入、更新操作的触发条件设置及执行逻辑。同时，通过具体示例展示了触发器在电话回拨任务调度、成功与失败结果处理中的具体实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE OR REPLACE TRIGGER triggerOnEvaluation
AFTER INSERT ON Evaluation  for each ROW

declare
  phonenumber varchar(50);
 recordCaseID1 varchar(20);
 recordCaseID2 varchar(20);  callOutUrl varchar(150);
  begin
   recordCaseID1:=:new.recordcaseID1;
    recordCaseID2:=:new.recordcaseID2;phonenumber:=:new.recordTel;
 
 
 
 callOutUrl:='http://10.33.113.39:8080/webVxml/CallOutDefault'||:new.recordCaseID1||'.html';
 
 if(mod(to_number(recordCaseID1),2)!=0) then
  INSERT INTO  TS_CALLOUT (TS_TASK_ID ,TS_TYPE ,TS_CALLED ,TS_URL ,TS_START_TIME ,TS_SCHEDULE_TIME,TS_EXPIRED_TIME ,TS_TIMES  ,
   TS_BUSY_INTERVAL,TS_NOANSWER_INTERVAL ,TS_OTHER_INTERVAL ,TS_ALREADY_TIMES ,TS_STATUS,TS_HOST)
     VALUES (recordCaseID1 ,recordCaseID2 ,phonenumber ,callOutUrl, trunc(sysdate+1)+9/24,
          trunc(sysdate+1)+10/24,add_months(sysdate,10),1,200 ,200 ,200 ,0 ,0 ,'');


    


      INSERT INTO  TS_CALLOUT (TS_TASK_ID ,TS_TYPE ,TS_CALLED ,TS_URL ,TS_START_TIME ,TS_SCHEDULE_TIME,TS_EXPIRED_TIME ,TS_TIMES  ,
   TS_BUSY_INTERVAL,TS_NOANSWER_INTERVAL ,TS_OTHER_INTERVAL ,TS_ALREADY_TIMES ,TS_STATUS,TS_HOST)
     VALUES (recordCaseID1 ,recordCaseID2 ,phonenumber ,callOutUrl, trunc(sysdate+1)+12.5/24,
          trunc(sysdate+1)+19/24,add_months(sysdate,10),1,200 ,200 ,200 ,0 ,0 ,'');
 else
 
      INSERT INTO  TS_CALLOUT (TS_TASK_ID ,TS_TYPE ,TS_CALLED ,TS_URL ,TS_START_TIME ,TS_SCHEDULE_TIME,TS_EXPIRED_TIME ,TS_TIMES  ,
   TS_BUSY_INTERVAL,TS_NOANSWER_INTERVAL ,TS_OTHER_INTERVAL ,TS_ALREADY_TIMES ,TS_STATUS,TS_HOST)
     VALUES (recordCaseID1 ,recordCaseID2 ,phonenumber ,callOutUrl, trunc(sysdate+1)+14/24,
          trunc(sysdate+1)+12.5/24,add_months(sysdate,10),1,200 ,200 ,200 ,0 ,0 ,'');


      INSERT INTO  TS_CALLOUT (TS_TASK_ID ,TS_TYPE ,TS_CALLED ,TS_URL ,TS_START_TIME ,TS_SCHEDULE_TIME,TS_EXPIRED_TIME ,TS_TIMES  ,
   TS_BUSY_INTERVAL,TS_NOANSWER_INTERVAL ,TS_OTHER_INTERVAL ,TS_ALREADY_TIMES ,TS_STATUS,TS_HOST)
     VALUES (recordCaseID1 ,recordCaseID2 ,phonenumber ,callOutUrl, trunc(sysdate+1)+19/24,
          trunc(sysdate+1)+19/24,add_months(sysdate,10),1,200 ,200 ,200 ,0 ,0 ,'');
 
   end if;
  end;

触发器二


if (object_id('trigCallOutResultSuccess', 'trigger') is not null)
    drop trigger trigCallOutResultSuccess
go
create trigger trigCallOutResultSuccess
on TS_CALLOUT_SUCCESS
    after  insert,update
as
    
  declare   @recordKey1 varchar(20);
	select @recordKey1=TS_TASK_ID from inserted;
  update Evaluation set recordResult=0 where recordcaseID1=@recordKey1;
   go

   if (object_id('trigCallOutResultFail', 'trigger') is not null)
    drop trigger trigCallOutResultFail
go
create trigger trigCallOutResultFail
on TS_CALLOUT_FAIL
    after  insert,update
as
    
  declare   @recordKey1 varchar(20);
	select @recordKey1=TS_TASK_ID from inserted;
  update Evaluation set recordResult=-1 where recordcaseID1=@recordKey1;
   go

CREATE OR REPLACE TRIGGER tigTS_AGE_LOG_GRO_Del
AFTER insert or update or delete ON TS_AGENT_LOG_GROUP for each ROW
declare
beginT date;

begin

if inserting then
beginT:=:old.ts_begin_time;
elsif updating then
beginT:=:old.ts_begin_time;
elsif deleting then

Insert into TS_AGENT_LOG_GROUP_BAK(ts_log_id,ts_category,TS_AGENT_ID,TS_THIS_PARTY,TS_OTHER_PARTY,TS_BEGIN_TIME,TS_PICKUP_TIME,TS_END_TIME,TS_END_CAUSE,TS_SOURCE_ROUTE,TS_TRANSFER_GROUP,TS_EXTION,
TS_HOLD_CALL_TIME,TS_SERVERFLAG,TS_ANSWER_PARTY,TS_RING_TIME,TS_RING_PARTY,TS_ID) values(:old.ts_log_id,
:old.ts_category,:old.ts_agent_id,:old.ts_this_party,:old.ts_other_party,:old.ts_begin_time,:old.ts_pickup_time,:old.ts_end_time
,:old.ts_end_cause,:old.ts_source_route,:old.ts_transfer_group,:old.ts_extion,:old.ts_hold_call_time,:old.ts_serverflag,:old.ts_answer_party,:old.ts_ring_time,
:old.ts_ring_party,:old.ts_id)
;
end if;
end;