Re: General PL/SQL question

I've done this a number of times without problem. You may want to check with OWS for any relevant bugs.

Also, your trigger should catch deletes.

Jared

On Fri, 2 Jun 2000, Kevin Martin wrote:

> Hi gurus.
>
> I have a rather general PL/SQL question. What is the best way to audit all
> of the inserts or updates for a particular table? I assumed that I could
> accomplish this by putting a row-level trigger (after insert or update) to
> capture all of the values for the row and write them to a 'history' table,
> but I am receiving a 'mutating table' error.
>
> Urman's "Oracle PL/SQL Programming" suggests using a PL/SQL table within a
> package. Are there any other options?
>
> The table is defined as below, and I have triggers to insert values for ID
> (before insert) and last_status_update (before insert or update). The audit
> trigger is below as well.
>
>
> CREATE TABLE WORKFLOW (
> ID NUMBER (12)
> CONSTRAINT PK_WORKFLOW_ID PRIMARY KEY ,
> ACCT_NO VARCHAR2 (10) NOT NULL,
> WORKFLOW_STAGE_ID NUMBER (3) NOT NULL,
> WORKFLOW_STATUS_ID CHAR (1) NOT NULL,
> PRIORITY NUMBER (3) NOT NULL,
> IN_TIME DATE DEFAULT SYSDATE NOT NULL,
> LAST_STATUS_UPDATE DATE NOT NULL,
> DOCUMENT_ID NUMBER(12),
> OWNER VARCHAR2 (8),
> COMMENTS VARCHAR2(30),
> UPDATED_BY VARCHAR2(30),
> VIEWED_YN CHAR(1) NOT NULL)
> /
>
>
>
> CREATE OR REPLACE trigger workflow_audit_aupd
> after insert or update on workflow
> for each row
> BEGIN
> INSERT into workflow_history
> (workflow_id, acct_no, workflow_stage_id,
> workflow_status_id,
> priority, in_time, last_status_update, document_id, owner,
> comments, updated_by, viewed_yn)
> values
> (:NEW.id, :NEW.acct_no, :NEW.workflow_stage_id,
> :NEW.workflow_status_id,
> :NEW.priority, :NEW.in_time, :NEW.last_status_update,
> :NEW.document_id,
> :NEW.owner, :NEW.updated_by, :NEW.viewed_yn);
> END workflow_audit_aupd;
> /
>
>
>
> Sincere thanks for your input.
> -km
>
> --
> Author: Kevin Martin
> INET: kevin.martin_at_catapultsystems.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon Received on Mon Jun 05 2000 - 10:50:43 CDT