开发者

Access to a row, which started a trigger

开发者 https://www.devze.com 2023-02-23 06:04 出处:网络
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

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消