Lists: | pgsql-sql |
---|
From: | "David Hofmann" <mor4321(at)hotmail(dot)com> |
---|---|
To: | bruno(at)wolff(dot)to |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Rule |
Date: | 2005-06-08 20:53:38 |
Message-ID: | BAY23-F1C805B1E550EF619E5F45BBFD0@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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
>Normally you want to do that with a before trigger rather than a rule.
_________________________________________________________________
FREE pop-up blocking with the new MSN Toolbar get it now!
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
From: | Keith Worthington <KeithW(at)NarrowPathInc(dot)com> |
---|---|
To: | David Hofmann <mor4321(at)hotmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Rule |
Date: | 2005-06-08 21:50:20 |
Message-ID: | 42A7681C.9060007@NarrowPathInc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
>>>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