Обсуждение: 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