Re: PL/SQL

We had this exact requirement a couple of years back. I didn't manage to strictly avoid the hard-coding, instead I generated the triggers from the data dictionany definition of the columns in the table, using the ordinal number of the column as the array index when inserting the value into an array.

In fact I used three sparse arrays (on for each major data-type - NUMBER, DATE, VARCAR)
passed into a short, but complex packaged procedure which dealt with all comparisions etc. The package is about 120 lines with the trigger generator being a couple of hundred. But this can be run on any table

Simon.

PS If you need more detail please email me direct.

BobGreat wrote:

> I am working with Oracle7 Workgroup Server on NT4.
> I an creating a trigger which will compare new value with old value for
> each field in the table and then record them in the log table if they differ.
> The syntax for new & old values is - :new.Employer_Name and
> old:Employer_Name where Employer_Name is name of the field.
>
> How I could compare values for each field without hardcoding field name
> (let's say loading all fields names in the array & then scan through
> this array) ?
>
> For example:
>
> var1 := 'Employer_Name';
> and then use var1 in :old and :new constructions . How ?
>
> Thanks in advance.
> E-Mail me to bobgreat_at_aol.com
> Boris Milrud.
Received on Wed Jul 01 1998 - 16:52:58 CDT