RE: General PL/SQL question

From: Gogala, Mladen <MGogala_at_oxhp.com>
Date: Fri, 2 Jun 2000 13:11:08 -0400
Message-Id: <10516.107587@fatcity.com>

Of course, you can always use the good, old database auditing, which is reliable and well documented.

-----Original Message-----
From: Kevin Martin [mailto:kevin.martin_at_catapultsystems.com] Sent: Friday, June 02, 2000 12:15 PM
To: Multiple recipients of list ORACLE-L Subject: General PL/SQL question

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

Received on Fri Jun 02 2000 - 12:11:08 CDT