General PL/SQL question

From: Kevin Martin <kevin.martin_at_catapultsystems.com>
Date: Fri, 2 Jun 2000 09:59:24 -0500
Message-Id: <10516.107556@fatcity.com>

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; Received on Fri Jun 02 2000 - 09:59:24 CDT