Thread: Need your help
Hi, I need your help on something. I have to write a trigger (in sybase) that does this: Everytime a record gets updated it should update a column in that record with the current date/time. I am able to do it for the whole table, but how do I make sure the update happens only for that record which is being updated? Please respond. Thanks a bunch, Jyotsna. __________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
e.g. Try: CREATE TABLE emp ( id int4 primary key, empname text, salary int4, last_date datetime, last_user name); CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS BEGINupdate emp set last_date=''now''::timestamp where id=NEW.id; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER emp_stamp BEFORE UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp(); Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.stbernard.com www.ipinc.com On Mon, 19 Feb 2001, Jyotsna Kypa wrote: > Hi, > I need your help on something. I have to write a > trigger (in sybase) that does this: Everytime a record > gets updated it should update a column in that record > with the current date/time. I am able to do it for the > whole table, but how do I make sure the update happens > only for that record which is being updated? Please > respond. > Thanks a bunch, > Jyotsna. > > > __________________________________________________ > Do You Yahoo!? > Get personalized email addresses from Yahoo! Mail - only $35 > a year! http://personal.mail.yahoo.com/ >
Jie Liang wrote: > e.g. > Try: > > CREATE TABLE emp ( > id int4 primary key, > empname text, > salary int4, > last_date datetime, > last_user name); > > CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS > BEGIN > update emp set last_date=''now''::timestamp where id=NEW.id; > RETURN NEW; > END; > ' LANGUAGE 'plpgsql'; > > CREATE TRIGGER emp_stamp BEFORE UPDATE ON emp > FOR EACH ROW EXECUTE PROCEDURE emp_stamp(); Not sure if it works this way at all, but the update on emp table is definitely a wasted scan. And the 'now'::timestamp will be evaluated at the first function call - never again; not sure if he wanted that behaviour either. CREATE FUNCTION emp_stamp () RETURNS opaque AS ' BEGIN new.last_date := now(); RETURN new; END;' LANGUAGE 'plpgsql'; Is the correct trigger for this purpose. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com