I know there're ways to access to query type, table names, oids etc. in trigger definitions:
http://www.postgresql.org/docs/8.3/static/plpgsql-trigger.html
Anyway, is there any chance that i run an UPDATE on a row, which started the trigger without actualy need to marking each row in the table by unique id?
e.g. if i have table for storing logs, i don't 开发者_高级运维need any unique id here... and as the time goes there may actually appear some rows, which will be equal.
CREATE TABLE users_log
(
uid bigint NOT NULL,
event smallint NOT NULL,
source character varying,
event_time timestamp with time zone
)
I know, that due to microseconds precision in "timestamp with time zone" data type is this situation nearly impossible, but not really impossible...
So how shall i write the query in trigger to be able to UPDATE just the inserted row?
$BODY$BEGIN
UPDATE "users_log" SET "event_time" = now(); -- this updates all rows
-- WHERE "id" = NEW.id; - this is what i don't want
RETURN NEW;
END;$BODY$
You just use in your trigger:
$BODY$BEGIN
NEW.event_time = now();
RETURN NEW;
END;$BODY$
And it just works — no need for additional update. You need to declare this trigger as BEFORE TRIGGER though.
精彩评论