Re: Firing trigger cuts db connection

On Jul 23, 11:34 am, freecycle <f.cy..._at_yahoo.com> wrote:
> Hi,
> I've created a trigger using the script below.
> whenever I fire it by the statement
> update site_control_t set oid = oid;
> I get
> ERROR at line 1:
> ORA-03113: end-of-file on communication channel
> and I'm no longer connected to Oracle.
> Can anyone suggest a reason please, this is driving me crazy!
> Thanks in advance,
> |FC
>
> create or replace trigger site_control_t_update_trg
> before update on site_control_t for each row
> declare
> maxruntime number(12):=0;
> retentiontime number(12):=0;
> maxcasedate char(8);
> casedate char(8);
> startrun date;
> endrun date;
> startloop date;
> endloop date;
> candidates number(12):=0; -- cases in cleardown_comments
> deletions number(8):=0; -- cases deleted
> casecount number(8):=0; -- cases remaining
> sql_statement varchar(1000);
> begin
> startrun:=sysdate;
> maxruntime:= 1; -- minutes
> retentiontime:= 36; -- months
> maxcasedate:=to_char(sysdate - round(retentiontime * 365.25 /
> 12),'YYYYMMDD');
> delete from cleardown_comments;
> insert into cleardown_comments
> select case_number,
> substr(max(date_time),1,8)
> from case_comment_t
> group by case_number
> having (substr(max(date_time),1,8) <= maxcasedate)
> and (substr(case_number,1,8) <= maxcasedate);
> select count(*) into candidates from cleardown_comments;
> endrun:=sysdate;
> casedate:='';
> while (trunc(to_number(endrun-startrun)*1440) < maxruntime) --
> main loop
> and (candidates > 0)
> loop
> startloop:=sysdate;

[]
> select count(*) into candidates from cleardown_comments;
> endrun:=sysdate;
> end loop;
> end;
> /
> show errors
> commit;

To repeat Dan Blum: that's one heck of a trigger. I'd bet it is due to waiting because of all the DELETE operations going on. If two sessions fire the trigger, and the first could take long enough to cause the second to time out.

It really looks like this logic possibly should be in a regular procedure, maybe with the trigger loading a control table of what cases to delete and insert. If you need to limit the trigger's runtime (see the WHILE statement) then it's not a trigger any more.

  Ed Received on Wed Jul 23 2008 - 12:31:47 CDT