Re: How do refer to the whole record in triggers
Date: 1996/08/29
Message-ID: <3225405D.1C36_at_olywa.net>#1/1
First, you should really work on getting those column names changed! Those are reserved words, and should never be used for column names.
However in the interim, the simple solution (though not robust) is to just leave off the column specification in the INSERT statement. Something like:
insert into master_table_jn
values
(:new.field1, :new.field2,...,:new.BEGIN, :new.END,...);
should work. However, you must specify the field names in the values clause in the same order the columns are created in the database (do a describe on the table to see). Also, you must have a field in the values clause for EVERY column in the table, which should not be a problem for your particular use. Usually it is much better to specifically call out the column names in the INSERT statement, but this is one case you may want to make an exception (at least until you get those column names fixed!!)
Ken Atkins - Senior Oracle Consultant - ARIS Corporation (katkins_at_ariscorp.com)
In an effort to contribute some content to the Web I have created an 'Oracle Tip of the Week' site:
http://www.olywa.net/katkins/oratip
Check it out!
Anantha Kasetty wrote:
>
> Hi,
> I am very new to oracle (the whole rdbms scene itself). I have a
> master record and I have to maintain the audit logs for changes done to
> the master table. This includes any update, insertion and deletions
> done on the master record. Everything is fine till here, but when I
> started writing the trigger, I realized that there were two fields in
> the master tables named "BEGIN" and the other one was "END". (I know, I
> know, very very bad choice of name for the columns, but I do not have
> any leeway, in changing them). So when I wrote the trigger, SQL*PLUS
> gave me a "Warning: compilation of trigger .." (something like that).
>
> I realized the problem, I was referring to the BEGIN and END fields
> in the trigger body ( a portion of it follows):
> IF INSERTING THEN
> insert into master_table_jn
> (field1, field2,..., BEGIN, END,...)
> values
> (:new.field1, :new.field2,...,:new.BEGIN, :new.END,...);
> END IF;
>
> If I remove the BEGIN and END column oracle is happy, but if I include
> them, it complains ( I think the BEGIN and END screw up the PL/SQL
> compiler). My question : Is there any way by which I can refer the
> affected record on the whole without specifying its individual fields.
> Like for example
> IF INSERTING THEN
> insert into master_table_jn
> values :new.whole_record;
> END IF;
>
> If this is not possible, is there anything, short of changing the
> column names, solve my probelm. I would appreciate any help on this.
> Please post your replies to either the newsgroup or to my email address,
> which is anantha_at_best.com
>
> thanks,
> anantha
Received on Thu Aug 29 1996 - 00:00:00 CEST