Обсуждение: Trigger to identify which column(s) updated

Does anyone know how to write a trigger that would identify which columns
have actually changed in an update (and then log them to an archive).

I suspect that the function would look something like;

CREATE FUNCTION FIND_CHANGED_COLUMNS() RETURNS OPAQUE AS '
BEGIN
  -- FOR EACH COLUMN IN THE RECORD:     -- IF ( NEW COLUMNx <> OLD COLUMNx)        -- LOG THE RECORD PRIMARY KEY,
COLUMNNAME, OLD VALUE
 
  RETURN NEW;
END;
' LANGUAGE 'plpgsql';

In other words- How might you parse, in general, old and new records to compare like
columns?     - How can you find out the primary key of a record?

Thanks, Jack

Dear Jack ,

>I suspect that the function would look something like;
>
>CREATE FUNCTION FIND_CHANGED_COLUMNS() RETURNS OPAQUE AS '
>BEGIN
>
>   -- FOR EACH COLUMN IN THE RECORD:
>      -- IF ( NEW COLUMNx <> OLD COLUMNx)
>         -- LOG THE RECORD PRIMARY KEY, COLUMN NAME, OLD VALUE
>
>   RETURN NEW;
>END;
>' LANGUAGE 'plpgsql';
>  
>

Instead of using a trigger use a rule as
<code>


</code>
CREATE RULE log_allthat  AS ON UPDATE TO table_to_scan  WHERE 
((NEW.coloum_1 !=  old.NEW.coloum_1) OR (NEW.coloum_2 !=  
old.NEW.coloum_2)) OR (MORE COLUMNS DO
INSERT INTO log_changes_table (

PRIMARY_KEY,COLUMN_NAME,OLD_VALUE

) VALUES (

OLD.PRIMARY_KEY,OLD.COLUMN_NAME,OLD.OLD_VALUE

);


Yes you will have to create a table as log_changes_table or any name you 
fancy such that data could be loged
</code>

Kindly shoot back if this helps.

-- 
Regards,
Vishal Kashyap

~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
I Know you believe my words so logon to Jabber.org
and add vishalkashyap@jabber.org to your roster.
~*~*~*~*~*~*~*~*
I am usually called by the name Vishal Kashyap
but my Girl friend believes my name should be
Vishal CASH UP.This is because others love my
nature and my Girl friend loves my CASH.
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*

Dear Jack  ,

>for each column in new:
>    if (new.column(1) != old.column(1)) ...
>
>      ...and then some snippet of code to determine the primary key column
>of new (or old)
>  
>
Check the following link

http://gborg.postgresql.org/project/tablelog/projdisplay.php

Hope this helps



-- 
Regards,
Vishal Kashyap

~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
I Know you believe my words so logon to Jabber.org
and add vishalkashyap@jabber.org to your roster.
~*~*~*~*~*~*~*~*
I am usually called as Vishal Kashyap
but my Girl friend calls me as Vishal CASH UP.
This is because others know me because of my generosity
and my Girlfriend knows me because of my CASH.
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*