Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Comparison of whole records

Re: Comparison of whole records

From: Bricklen Anderson <bricklen13_at_hotmail.com>
Date: 25 Jan 2002 09:02:40 -0800
Message-ID: <b416ca2d.0201250902.19e47a35@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US