RE: Using procedures instead of coding update/insert

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Tue, 15 Jan 2002 12:55:05 -0800
Message-ID: <F001.003F0763.20020115112529@fatcity.com>

Chris,

It is not that difficult to generate the code.

I recently wrote a procedure that generates code to insert into a table, you provide the owner and table name. I wanted to create generic audit triggers ... but that is too much of work, so I wrote some code that would generate the audit trigger.

1. The procedure GENTRIGREC accepts owner and table name and a optional parameter (more on that later).
2. It generates code required for auditing, 2.1 it declares two records of table%rowtype, calls them NEW and OLD to hold appropriate values. 2.2 based on what the action is, it populates NEW and/or OLD records with appropriate values. 2.3 based on action that fired the trigger, it defines a variable called action. 2.4 if optional parameter (as in 1) is set to true, it generates code that lets you capture a list of ALL the changed columns in the current table into a variable.
3. Then it is up to you to pass the records, the action variable and list of updated columns (in case of update statement) to a procedure to insert into a audit statement.

Then you call GENAUDPROC procedure with name of the intended procedure and it generates generic procedure code. This you can then tweak to suit your needs. This actually will reduce a whole lot of work for developers.

Our idea is to audit DML and DDL on core tables for the applications. The audit table will be owned by a special audit_user. Everyone else will have select and insert privilege on the audit tables (no update and delete privileges will be granted).

If anyone would like to test this, let me know, that would help me too to improve my code. In the next release of this code I am planning to provide some checks that can identify if the table structure has been modified so appropriate trigger and associated audit procedure can be regenerated to accommodate the table change.

Cheers
Raj

---

Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----
Sent: Tuesday, January 15, 2002 1:20 PM
To: Multiple recipients of list ORACLE-L

A small con for the PL/SQL procedures is that I have to rebuild the procedure for a table when the table structure changes(column added/dropped). Granted, I could probably build a more dynamic upd/del/ins procedure for each table, but then I am trying to keep the procedure code tight.

Also, I noticed that the current version does some basic checks before executing the INSERT/UPDATE/DELETE SQL. For example, it checks to see if the record exists before executing the INSERT sql.

*********************************************************************2

This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.

*********************************************************************2
Received on Tue Jan 15 2002 - 14:55:05 CST