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

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 Received on Wed Jan 09 2008 - 12:24:41 CST