Thread: Triggers and Functions

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...

am  Fri, dem 24.11.2006, um  7:25:16 -0500 mailte Tom Allison folgendes:
> I would like to create a function/trigger which will update a timestamp on
> table A when an UPDATE is executed against table B
> 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...

I think, you should better create a RULE like this:

create rule update_rule as on update to user_token do also update tokens set last_seen = now() where token_idx =
NEW.token_idx;

Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

A. Kretschmer wrote:
> am  Fri, dem 24.11.2006, um  7:25:16 -0500 mailte Tom Allison folgendes:
>> I would like to create a function/trigger which will update a timestamp on
>> table A when an UPDATE is executed against table B
>> 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...
>
> I think, you should better create a RULE like this:
>
> create rule update_rule as on update to user_token do also update tokens set last_seen = now() where token_idx =
NEW.token_idx;
>
> Andreas


This works perfectly.