Re: When inserting a bad record, send the error record to another table with Oracle Error

On Jan 9, 1:24 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Jan 9, 10:44 am, zip <zipRobe..._at_gmail.com> wrote:
>
>
>
>
>
> > Say I have a table SCOTT.EMP and when someone inserts into the table
> > and incurrs and error (Violated PK, FK constraints, etc) I want that
> > record to write to an EMPAUDIT table so I can review it.   I wrote
> > this trigger that I thought would do the job, but it does not.  My
> > next step would be to add a field to EMPAUDIT called ERRMSG and write
> > the SQLERRM to the table too.  Just trying to get this to work first.
>
> > CREATE OR REPLACE TRIGGER  empTrg
> > AFTER INSERT
> > ON emp
> > FOR EACH ROW
> > BEGIN
> >   NULL;
>
> > EXCEPTION
> >  WHEN others then
> >    INSERT INTO empaudit(EMPNO, ENAME, JOB, MGR,HIREDATE, SAL, COMM,
> > DEPTNO ) VALUES
> > (:new.EMPNO, :new.ENAME, :new.JOB, :new.MGR,:new.HIREDATE, :new.SAL, :new.COMM, :new.DEPTNO);
>
> > END empTrg ;
> > /
>
> > Here is EMPAUDIT
>
> >  EMPNO
> >  ENAME
> >  JOB
> >  MGR
> >  HIREDATE
> >  SAL
> >  COMM
> >  DEPTNO
> >  AUDITDATE
> >  AUDITCODE
>
> > The trigger compiles, a PK violation shows up in SQLPLUS, but the
> > record does not go to EMPAUDIT with this trigger.
>
> The trigger isn't generating the error, the insert is.  As such you'll
> never get to the EXCEPTION section so you won't get any records in
> EMPAUDIT.  You could write a procedure to perform the insert and use
> your EXCEPTION block in that instead; the procedure would generate the
> ORA-00001 error and then execute the EXCEPTION section, inserting your
> audit record into the EMPAUDIT table.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

It is not good practice to use the WHEN OTHERS clause when only specific errors should be caught. In this case the predefined DUP_VAL_ON_INDEX error. What happens if the problem is "unable to extend" because the tablespace, perhaps even the same one that holds the error table, is full?

HTH -- Mark D Powell -- Received on Wed Jan 09 2008 - 15:32:20 CST