pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger to identify which column(s) updated
3+ messages / 3 participants
[nested] [flat]
* Trigger to identify which column(s) updated
@ 2004-01-15 21:15 Jack Kerkhof <jack.kerkhof@guest-tek.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jack Kerkhof @ 2004-01-15 21:15 UTC (permalink / raw)
To: pgsql-sql
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, COLUMN NAME, 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trigger to identify which column(s) updated
@ 2004-01-18 22:29 Vishal Kashyap @ [Sai Hertz And Control Systems] <sank89@sancharnet.in>
parent: Jack Kerkhof <jack.kerkhof@guest-tek.com>
0 siblings, 0 replies; 3+ messages in thread
From: Vishal Kashyap @ [Sai Hertz And Control Systems] @ 2004-01-18 22:29 UTC (permalink / raw)
To: Jack Kerkhof <jack.kerkhof@guest-tek.com>; +Cc: pgsql-sql
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.
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trigger to identify which column(s) updated
@ 2004-01-20 05:27 V i s h a l Kashyap @ [Sai Hertz And Control Systems] <sank89@sancharnet.in>
0 siblings, 0 replies; 3+ messages in thread
From: V i s h a l Kashyap @ [Sai Hertz And Control Systems] @ 2004-01-20 05:27 UTC (permalink / raw)
To: Jack Kerkhof <jack.kerkhof@guest-tek.com>; +Cc: pgsql-sql
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.
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-01-20 05:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-15 21:15 Trigger to identify which column(s) updated Jack Kerkhof <jack.kerkhof@guest-tek.com>
2004-01-18 22:29 ` Vishal Kashyap @ [Sai Hertz And Control Systems] <sank89@sancharnet.in>
2004-01-20 05:27 Re: Trigger to identify which column(s) updated V i s h a l Kashyap @ [Sai Hertz And Control Systems] <sank89@sancharnet.in>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox