Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Comparison of whole records
If I understand you correctly, you want to log the updates (or
whatever) when values are changed in the tables. your triggers can be
set to (at row level) fire only when certain columns are affected, so
you can use a WHEN condition or an if/then/else statement in your
trigger.
something like this may work for you:
CREATE OR REPLACE TRIGGER update_certain_logtable
AFTER INSERT OR UPDATE ON table_1
FOR EACH ROW
WHEN (new.certain_column != old.certain_column) --like an if statement
DECLARE
your_value NUMBER(6);
err_msg VARCHAR2(1200);
id NUMBER(12);
BEGIN
UPDATE log_table_1 SET your_value = :new.your_value+:old.your_value
WHERE :new.id= table_1.id;
EXCEPTION
WHEN OTHERS THEN
err_msg := sqlerrm;
INSERT INTO errorlog
VALUES (err_errlogid_seq.nextval,'Error','trigger
update_certain_logtable failed to fire properly.',err_msg,sysdate);
END update_certain_logtable;
/
show errors
"Patrick J." <patrickREMOVE.jTHIS_at_kneip.com> wrote in message news:<3c5037f8$1_2_at_news.vo.lu>...
> Hi,
>
> On an Oracle 8.1.7 database, I have created triggers that maintain an
> history of all changes in every tables. But some processes are issuing
> updates on several records without changing data in them, this resulting in
> unnecessary overloading of history tables.
>
> Is there a way in a trigger to compare :old and :new records, without
> comparing each field of the record?
>
> Thanks,
>
> Patrick J.
Received on Fri Jan 25 2002 - 11:02:40 CST