Обсуждение: automatic time/user stamp - rule or trigger?
I have a table that I want to keep track of the user who last modified it and the timestamp of the modification. Should I use a trigger or a rule? CREATE TABLE stampedtable (stampedtableid SERIAL PRIMARY KEY,updatestamp timestamp NOT NULL DEFAULT now(),updateuser nameNOT NULL DEFAULT current_user,sometext text ); I suspect that I want a rule, but all the examples in the documentation seem to update a separate table and not override (or add) the insert/update to the timestamp and name columns. Thanks, -Neal
On Wed, 5 Feb 2003, Neal Lindsay wrote: > I have a table that I want to keep track of the user who last modified > it and the timestamp of the modification. Should I use a trigger or a rule? > > CREATE TABLE stampedtable ( > stampedtableid SERIAL PRIMARY KEY, > updatestamp timestamp NOT NULL DEFAULT now(), > updateuser name NOT NULL DEFAULT current_user, > sometext text > ); > > I suspect that I want a rule, but all the examples in the documentation > seem to update a separate table and not override (or add) the > insert/update to the timestamp and name columns. You may want to use rules if you need rewriting. What you actually need is some sort of driver to a specific table. You could create a view to that table (to hide the accounting columns), and then create rules on that view that do the job as you wish. > > Thanks, > -Neal > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
Achilleus Mantzios wrote: > > On Wed, 5 Feb 2003, Neal Lindsay wrote: > > > I have a table that I want to keep track of the user who last modified > > it and the timestamp of the modification. Should I use a trigger or a rule? > > > > CREATE TABLE stampedtable ( > > stampedtableid SERIAL PRIMARY KEY, > > updatestamp timestamp NOT NULL DEFAULT now(), > > updateuser name NOT NULL DEFAULT current_user, > > sometext text > > ); > > > > I suspect that I want a rule, but all the examples in the documentation > > seem to update a separate table and not override (or add) the > > insert/update to the timestamp and name columns. > > You may want to use rules if you need rewriting. > What you actually need is some sort of driver to a specific table. > You could create a view to that table (to hide the accounting columns), > and then create rules on that view that do the job as you wish. I'm sure you want to use a BEFORE INSERT OR UPDATE trigger that modifies NEW.updatestamp in place just before the row get's written. A rule will not work here because rules cannot cause the same action on the same table they are called for. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: > A rule will not work here because rules cannot cause the same action on > the same table they are called for. > A-ha! I guess that's what I wasn't understanding. Triggers it is then. Thanks, -Neal
You're doing update right ??? Just update the column... It's even easier if, when you do your updates... You just: UPDATE blah SET field = value,...., updatestamp = 'Now()' WHERE condition... GP "Neal Lindsay" <neal.lindsay@peaofohio.com> wrote in message news:b1r864$2mpp$1@news.hub.org... > I have a table that I want to keep track of the user who last modified > it and the timestamp of the modification. Should I use a trigger or a rule? > > CREATE TABLE stampedtable ( > stampedtableid SERIAL PRIMARY KEY, > updatestamp timestamp NOT NULL DEFAULT now(), > updateuser name NOT NULL DEFAULT current_user, > sometext text > ); > > I suspect that I want a rule, but all the examples in the documentation > seem to update a separate table and not override (or add) the > insert/update to the timestamp and name columns. > > Thanks, > -Neal >
On Wed, 5 Feb 2003, Jan Wieck wrote: > Achilleus Mantzios wrote: > > > > On Wed, 5 Feb 2003, Neal Lindsay wrote: > > > > > I have a table that I want to keep track of the user who last modified > > > it and the timestamp of the modification. Should I use a trigger or a rule? > > > > > > CREATE TABLE stampedtable ( > > > stampedtableid SERIAL PRIMARY KEY, > > > updatestamp timestamp NOT NULL DEFAULT now(), > > > updateuser name NOT NULL DEFAULT current_user, > > > sometext text > > > ); > > > > > > I suspect that I want a rule, but all the examples in the documentation > > > seem to update a separate table and not override (or add) the > > > insert/update to the timestamp and name columns. > > > > You may want to use rules if you need rewriting. > > What you actually need is some sort of driver to a specific table. > > You could create a view to that table (to hide the accounting columns), > > and then create rules on that view that do the job as you wish. > > I'm sure you want to use a BEFORE INSERT OR UPDATE trigger that modifies > NEW.updatestamp in place just before the row get's written. > > A rule will not work here because rules cannot cause the same action on > the same table they are called for. Thats why the view comes in place. > > > Jan > > -- > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #================================================== JanWieck@Yahoo.com # > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr