Thread: Help on some SQL command...
Hello to all the SQL gurus... I have this little table: test1 ( id char(8) primary key, name char(20), create_date timestamp ); Now, the create_date need to be updated to date and time whenever id is being inserted or updated. Is there a way in SQL that I can do this automatically? I try the following command using 'trigger' ---- CREATE_TRIGGER test1_autodate AFTER INSERT OR UPDATE ON test1 FOR EACH ROW EXECUTE update("test1") CREATE FUNCTION update(char *) RETURNS int4 AS 'UPDATE $1 set date="now"' LANGUAGE 'sql'; ---- But realized 'char *' is not allow... Is there a way to solve this? Or Am I the right track in solving this problem? Hope someone can help me... thanks. Sei Heng
Hi, You can specify a default value for the create_date column like: create_date timestamp default 'now' In the insert statement just ommit that field and you will get the default, as insert into test1 (id, name) values (1, 'xxx'); Regards, -- Guo Bin --- Ang Sei Heng <joanna@leopard.cybersource.com.sg> wrote: > Hello to all the SQL gurus... > > I have this little table: > > test1 ( > id char(8) primary key, > name char(20), > create_date timestamp > ); > > Now, the create_date need to be updated > to date and time whenever id is being > inserted or updated. > > Is there a way in SQL that I can do this > automatically? __________________________________________________ Do You Yahoo!? Send instant messages & get email alerts with Yahoo! Messenger. http://im.yahoo.com/
Use default now() -- Jesus Aneiros Sosa mailto:aneiros@jagua.cfg.sld.cu http://jagua.cfg.sld.cu/~aneiros On Wed, 16 Aug 2000, Ang Sei Heng wrote: > Hello to all the SQL gurus... > > I have this little table: > > test1 ( > id char(8) primary key, > name char(20), > create_date timestamp > ); > > Now, the create_date need to be updated > to date and time whenever id is being > inserted or updated. > > Is there a way in SQL that I can do this > automatically? > > I try the following command using 'trigger' > > ---- > CREATE_TRIGGER test1_autodate > AFTER INSERT OR UPDATE ON > test1 FOR EACH ROW EXECUTE update("test1") > > CREATE FUNCTION update(char *) RETURNS int4 > AS 'UPDATE $1 set date="now"' > LANGUAGE 'sql'; > ---- > > But realized 'char *' is not allow... Is there a > way to solve this? > > Or Am I the right track in solving this problem? > > Hope someone can help me... thanks. > > Sei Heng > >