Message-ID: <42A7681C.9060007@NarrowPathInc.com>
Date: Wed, 08 Jun 2005 17:50:20 -0400
From: Keith Worthington <KeithW@NarrowPathInc.com>
Reply-To: KeithW@NarrowPathInc.com
Organization: Narrow Path, Inc.
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: David Hofmann <mor4321@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Rule
References: <BAY23-F1C805B1E550EF619E5F45BBFD0@phx.gbl>
In-Reply-To: <BAY23-F1C805B1E550EF619E5F45BBFD0@phx.gbl>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>>>David wrote:
>>>
>>>What I want to do is setup some kind of rule so that whenever a s_data 
>>>field is updated, that the time_stamp gets update to the current time/date. 
>>
>> Normally you want to do that with a before trigger rather than a rule.
> 
> Ok, I have no knowledge of Tiggers except what I just read in the docs 
> section. Look like I need to make a procudure then call it with a trigger.
> 
> Is there a better location for Tigger/Procudure Examples. The trigger 
> seems fairly, however I got lost in the procudure part.
> 
> David

David,

Here is a trigger function that I wrote for storing audit information. 
Whether or not a query provides the user and/or timestamp this procedure 
sets them.  Naturally you will need to modify them for your data model.

CREATE OR REPLACE FUNCTION interface.tf_audit_data()
   RETURNS "trigger" AS
$BODY$
    BEGIN
--    Set the user name.
       SELECT * FROM session_user INTO NEW.audit_user;
--    Set the timestamp.
       NEW.audit_timestamp := ('now'::text)::timestamp(6) with time zone;
--    Send the modified record down the pipe.
       RETURN NEW;
    END;
$BODY$
   LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION interface.tf_audit_data() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION interface.tf_audit_data() TO postgres;
GRANT EXECUTE ON FUNCTION interface.tf_audit_data() TO public;

CREATE TRIGGER tgr_audit_data
   BEFORE INSERT OR UPDATE
   ON sales_order.tbl_line_item
   FOR EACH ROW
   EXECUTE PROCEDURE interface.tf_audit_data();

-- 
Kind Regards,
Keith