Skip site navigation (1) Skip section navigation (2)

Triggers and Functions

From: Tom Allison <tom(at)tacocat(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Triggers and Functions
Date: 2006-11-24 12:25:16
Message-ID: 4566E4AC.5060700@tacocat.net (view raw or flat)
Thread:
Lists: pgsql-novice
I would like to create a function/trigger which will update a timestamp on table 
A when an UPDATE is executed against table B

I tried this:


create table tokens (
   token_idx BIGSERIAL PRIMARY KEY,
   token varchar(128) not null unique,
   last_seen timestamp default now()
)

create table user_token (
   user_idx integer not null references users(user_idx) on delete cascade on 
update cascade,
   token_idx bigint not null references tokens(token_idx) on delete cascade on 
update cascade,
   h_msgs integer default 0,
   s_msgs integer default 0,
   constraint pkey_user_token PRIMARY KEY (user_idx, token_idx)
)

CREATE FUNCTION update_token(bigint) RETURNS void AS '
   UPDATE tokens SET last_seen = now()
   WHERE token_idx = $1 and last_seen < now();
' LANGUAGE SQL


CREATE TRIGGER touch_token AFTER UPDATE ON user_token FOR EACH ROW
EXECUTE PROCEDURE update_token(token_idx)


But it doesn't seem to change the filed 'last_seen' on the tokens table...

Responses

pgsql-novice by date

Next:From: A. KretschmerDate: 2006-11-24 12:45:41
Subject: Re: Triggers and Functions
Previous:From: Duncan GarlandDate: 2006-11-24 10:57:05
Subject: Re: Data (Table) Structure Question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group