Dear all,<br /><br />Please see SQL below. I'm already satisfied with everything except I wish in #4, mtime got
automaticallyupdated to NOW() if not explicitly SET in UPDATE statement. Is there a way to make the mtime column behave
morelike I wanted? Thanks in advance. <br /><br />create table t1 (<br /> id int primary key,<br /> t text,<br />
ctimetimestamp with time zone,<br /> mtime timestamp with time zone<br />);<br /><br />create or replace function
update_times()returns trigger as $$<br />begin <br /> if tg_op='INSERT' then<br /> if NEW.ctime is null then
NEW.ctime= NOW(); end if;<br /> if NEW.mtime is null then NEW.mtime = NOW(); end if;<br /> elsif tg_op='UPDATE'
then<br /> if NEW.ctime is null then NEW.ctime = OLD.ctime; end if;<br /> if NEW.mtime is null then NEW.mtime =
NOW();end if;<br /> end if;<br /> return NEW;<br />end;<br />$$ language plpgsql;<br /><br />create trigger
update_timesbefore update or insert on t1 <br />for each row execute procedure trig1(); <br /><br />-- #1. mtime &
ctimeset to NOW()<br />insert into t1 values (1,'text1',null,null);<br /><br />-- #2. mtime & ctime set to
'2001-01-01'<br/>insert into t1 values (2,'text2','2000-01-01','2000-01-01');<br /><br />-- #3. mtime and ctime set to
'2006-06-06'<br />update t1 set t='new text1',ctime='2006-6-6',mtime='2006-6-6' where id=1;<br /><br />-- #4. mtime and
ctimeunchanged<br />update t1 set t='new text1' where id=1;<br /><br />-- #5. mtime automatically updated to NOW()<br
/>updatet1 set t='new text1',mtime=null where id=1; <br /><br />
