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

Re: triggers

From: "Henshall, Stuart - WCP" <SHenshall(at)westcountrypublications(dot)co(dot)uk>
To: "'paul(at)entropiaco(dot)uk'" <paul(at)entropiaco(dot)uk>,pgsql-novice(at)postgresql(dot)org
Subject: Re: triggers
Date: 2002-04-22 12:57:39
Message-ID: E2870D8CE1CCD311BAF50008C71EDE8E01F74854@MAIL_EXCHANGE (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
> From: paul butler [mailto:polb(at)cableinet(dot)co(dot)uk]
> Dear list,
> Just starting out with postgresql, I've got a 18 table db 
> listing info 
> on organisations and on the main organisation table I've got an 
> update column (timestamp), to keep a record of the last time 
> information was updated for each organisation.
> Obviously I can do it on the client app, but I think table /row 
> triggers might be a better solution
> is there a straight forward way of, on updating any of the 
> organisation tables I can update the update column for that 
> organisation?
> eg On update orgsubtable wher orgId = X
> 	trigger update orgMainTable.timestamp Values(now()) where 
> orgId = X
> Paul Butler
I'd probably use an update rule to do this:
CREATE RULE example_rl AS ON UPDATE TO orgsubtable DO
	UPDATE orgmaintable SET tmstmp=now() WHERE orgid=OLD.orgid;

if both tables had orgid. You'll notice I changed the field name from
timestamp. timestamp is a datatype and (IIRC) a reserved word.
There is more information on triggers and rules in the manual
Hope this helps,
- Stuart

pgsql-novice by date

Next:From: Joshua b. JoreDate: 2002-04-22 13:13:27
Subject: Re: triggers
Previous:From: Mathieu ArnoldDate: 2002-04-22 07:58:15
Subject: Re: [NOVICE] Hardware needed for 15,000,000 record DB?

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