CODEWITHSUNDEEP
sqlpostgresqltriggersupdatesddl
gudge
gudge

Reputation: 1073

Specifying a triger to update timestamp field

This is a follow up question on : old example.

CREATE TABLE "x"."y"(
    "z" timestamp NOT NULL,
    "a" Timestamp NOT NULL DEFAULT z + 18 months,
);

I wish to specify a trigger where a is updated as soon as z is updated. Something on the lines of 

CREATE TRIGGER a_update
    AFTER UPDATE on z
    on x.y
    NEW.a = NEW.z + interval '18 months'

Could you please help me with the syntax?

Upvotes: 1

Views: 84

Answers (2)

IMSoP
IMSoP

Reputation: 97848

Triggers in PostgreSQL are implemented by first creating a function, and then attaching that function to the appropriate table, as documented in the Postgres manual.

The manual page for CREATE TRIGGER has this example, which should be easy to adapt to your actual names:

CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
    EXECUTE PROCEDURE check_account_update();

For the definition of the actual trigger function, you just need to fill out the body of this; the OLD and NEW aliases represent the old and new values of the whole row:

CREATE FUNCTION my_trigger() RETURNS trigger
    AS $$
        NEW.foo := OLD.foo + 1;
        RETURN NEW;
    $$
    LANGUAGE plpgsql;

There are more notes and examples on this manual page.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311583

Since a in your scenario should always be 18 months after z there's no point in actually storing it anywhere. It would be much easier to just have it as a calculated column in a view:

CREATE VIEW y_view AS 
SELECT z, z + INTERVAL '18 MONTH' AS a 
FROM   y;

Upvotes: 1

Related Questions