Quoth Adrian Klaver <aklaver@comcast.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
