Re: Detecting changes to certain fields in 'before update' trigger functions

Quoth Adrian Klaver <aklaver(at)comcast(dot)net>:
> On Monday 01 December 2008 7:18:51 am Sebastian Tennant wrote:
>> I had thought that OLD holds the record as it was before the update,
>> and that NEW holds the record as it is since the update (but before
>> the update has been committed)?

'42.10 Trigger Procedures' seems to confirm this:

"`NEW'
Data type `RECORD'; variable holding the new database row for
`INSERT'/`UPDATE' operations in row-level triggers. This variable
is `NULL' in statement-level triggers.

`OLD'
Data type `RECORD'; variable holding the old database row for
`UPDATE'/`DELETE' operations in row-level triggers. This variable
is `NULL' in statement-level triggers."

> It works here. Can you be more specific? Full function code, table schema,etc.

Of course.

######## timestamper.sql starts here ########
-- \i ./timestamper.sql

DROP TABLE IF EXISTS tt;
CREATE TEMP TABLE tt (username character varying(12),
delisted boolean,
created_at timestamp(0) without time zone,
updated_at timestamp(0) without time zone,
delisted_at timestamp(0) without time zone);

CREATE OR REPLACE FUNCTION timestamper() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN NEW.created_at := current_timestamp(0); END IF;
IF (TG_OP = 'UPDATE') THEN
NEW.updated_at := current_timestamp(0);
IF ((NEW.delisted = true) AND (NEW.delisted != OLD.delisted)) THEN
NEW.delisted_at := current_timestamp(0); END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER timestamper_before_insert BEFORE INSERT ON tt FOR EACH ROW
EXECUTE PROCEDURE timestamper();

CREATE TRIGGER timestamper_before_update BEFORE UPDATE ON tt FOR EACH ROW
EXECUTE PROCEDURE timestamper();

-- DROP FUNCTION timestamper() CASCADE;
-- no need to drop temporary tables

######## timesatmper.sql ends here ########

testdb=> \i ./timestamper.sql
DROP TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
CREATE TRIGGER
testdb=> insert into tt values (foo');
INSERT 0 1
testdb=> select * from tt;
-[ RECORD 1 ]--------------------
username | foo
delisted |
created_at | 2008-12-01 16:17:37
updated_at |
delisted_at |

testdb=> update tt set username=bar';
UPDATE 1
testdb=> select * from tt;
-[ RECORD 1 ]--------------------
username | bar
delisted |
created_at | 2008-12-01 16:17:37
updated_at | 2008-12-01 16:18:27
delisted_at |

testdb=> update tt set delisted=true where username='bar';
UPDATE 1
testdb=> select * from tt;
-[ RECORD 1 ]--------------------
username | bar
delisted | t
created_at | 2008-12-01 16:17:37
updated_at | 2008-12-01 16:19:01
delisted_at |

The triggers for the initial insert and the first update do what I want
them to, but the second update (that marks 'foo' as delisted) fails to
update the delisted_at timestamp.

Sebastian