Re: Firing trigger cuts db connection

freecycle 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 substr(min(case_number),1,8) -- get deletion date
> into casedate
> from cleardown_comments;
> delete from cleardown_cases;
> insert into cleardown_cases
> select ca.case_number
> from case_t ca,
> cleardown_comments co
> where (ca.case_number = co.case_number)
> and (ca.case_number <= casedate || '99999')
> and (ca.open_dt <= to_number(casedate));
>
> select count(*) into deletions from cleardown_cases;
> delete from case_address_t
> where case_number in (select * from cleardown_cases);
> delete from case_adjust_t
> where case_number in (select * from cleardown_cases);
> delete from case_advice_t
> where case_number in (select * from cleardown_cases);
> delete from case_comment_t
> where case_number in (select * from cleardown_cases);
> delete from case_eft_t
> where case_number in (select * from cleardown_cases);
> delete from case_field_t
> where case_number in (select * from cleardown_cases);
> delete from case_print_t
> where case_number in (select * from cleardown_cases);
> delete from case_prov_t
> where case_number in (select * from cleardown_cases);
> delete from case_research_t
> where case_number in (select * from cleardown_cases);
> delete from case_tran_t
> where case_number in (select * from cleardown_cases);
> delete from delete_queue_t
> where d_que_case_number in (select * from
> cleardown_cases);
> delete from interest_t
> where case_number in (select * from cleardown_cases);
> delete from res_queue_t
> where case_number in (select * from cleardown_cases);
> delete from research_det_t
> where case_number in (select * from cleardown_cases);
> delete from research_rltd_t
> where case_number in (select * from cleardown_cases);
> delete from research_susp_t
> where case_number in (select * from cleardown_cases);
> delete from case_t
> where case_number in (select * from cleardown_cases);
> delete from cleardown_comments
> where (case_number <= casedate || '99999');
> select count(*) into casecount from case_t;
> endloop:= sysdate;
> insert into cleardown_log values (
> to_char(sysdate,'YYYYMMDD HH24:MI:SS'),
> casedate,
> deletions,
> to_char(trunc(to_number(endloop-startloop)*24)) || ':'
> ||
> to_char(mod(trunc(to_number(endloop-startloop)*1440),
> 60)) || ':' ||
> to_char(mod(trunc(to_number(endloop-startloop)*86400),
> 60)),
> casecount);
> select count(*) into candidates from cleardown_comments;
> endrun:=sysdate;
> end loop;
> end;
> /
> show errors
> commit;

Your code makes no sense as a trigger. Consider what you've done. You want this trigger to fire for every row updated by an update statement.

So if someone writes a statement that updates a single row it fires once. If the statement updates 50 rows it fires 50 times. Does it make sense to you to delete every row in a table 50 times for a single update statement?

My recommendation would be to throw this code away and rather than showing us code or pseudocode ... state the business case. What is the business process you are trying to support. And write your explanation in business language.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org