Simple Trigger functionality [message #39326] |
Mon, 08 July 2002 03:09 |
Anil
Messages: 80 Registered: September 2001
|
Member |
|
|
Hi,
i have a rather simple looking problem .I have a set of tables
with Triggers written on each of them .These triggers fire after
an Update on each of these tables.
Now, i need to capture the changed columns in a table and
put them in another table( say CHANGE).
This can happen on all columns of each of the tables.
Right now, i have a cursor(COLUMN_NAME) that gets the
column_names of a table .
i have defined the cursor as
CURSOR COLUMN_NAME is
select COLUMN_NAME from cols where TABLE_NAME =
'TEST';
In the update trigger of each table , i have written this
cursor.The problem that i am facing is that my trigger doesn't
even compile when i write something like
this:
for record in COLUMN_NAME loop
if (:old.record.COLUMN_NAME !=
:new.record.COLUMN_NAME) then
dbms_output.put_line(record.COLUMN_NAME || 'has
changed');
end if;
end loop;
i think , i could have used something like
if (:old.ID != :new.ID ) then
dbms_output.put_line(ID || 'has changed');
end if;
where ID happens to be a column in the tale TEST.
But , i will have to repeat the same code for all the columns in
the table which i don't think is a good solution.
Can anybody please help me out in this.
Waiting eagerly for help
|
|
|
Re: Simple Trigger functionality [message #39328 is a reply to message #39326] |
Mon, 08 July 2002 04:28 |
|
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
if you use
:old.COLUMN_NAME or :new.column_name
it will treated as a trigger record.
trigger will expect a column called COLUMN_NAME in the base table. In order to provide conistency, oracle will not allow u this.
|
|
|