Thread: Rule
I have a table with 3 fields, id, s_data, and time_stamp. The time_stamp field is set to now() by deault. The program that uses this table only uses the id and s_data file. I added and use the time_stamp field to delete old records after a certain time. 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. The program regretab I'm not really familar with rules, I've only used them in a certain places and very limitedly. Any help would be greatly appercated. David Hofmann _________________________________________________________________ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
On Wed, Jun 08, 2005 at 15:51:35 -0400, David Hofmann <mor4321@hotmail.com> 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 >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/
>>>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