Re: Triggers/SPL Time Stamping
(Kerry: this is potential FAQ material)
>From: "John S. DeHart" <ht6@ornl.gov>
>Date: 9 Oct 1995 17:02:21 GMT
>X-Informix-List-Id: <news.17762>
>
>Does anyone know how to automatically update audit fields within the same
>table utilizing a Trigger or SPL ?? For the following table:
>
> create table test
> (
> important_data char(60) not null,
> up_date_time datetime year to fraction(3)
> default current year to fraction(3),
> up_user_id char(12)
> default user);>
> The default will work for the initial insert but will not handle the
>updates. Is it possible to create a SIMPLE Trigger/SPL to handle
>automatically updating up_date_time and up_user_id whenever a record is
>updated ?? We are currently utilizing ONLINE version 5.
Using a view as below makes it easy -- update the view and the audit trail
columns are updated too. Why use a view? If the audit columns are
mentioned in the UPDATE statement, then the values cannot be changed by the
trigger.
CREATE TABLE T1
(
key SERIAL NOT NULL,
data CHAR(20) NOT NULL,
u_date DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND NOT NULL,
u_user CHAR(8) DEFAULT USER NOT NULL
);
CREATE TRIGGER u_t1 UPDATE OF data ON T1
REFERENCING new AS new
FOR EACH ROW(UPDATE T1
SET (u_date, u_user) = (CURRENT YEAR TO SECOND, USER)
WHERE key = new.key);
CREATE VIEW V1(key, data)
AS SELECT key, data FROM T1;
INSERT INTO V1 VALUES(0, "abcdef-1");
INSERT INTO V1 VALUES(0, "abcdef-2");
INSERT INTO V1 VALUES(0, "abcdef-3");
SELECT * FROM T1; -- Check the data...SLEEP 3; -- Allow time to pass...
UPDATE V1 SET data = "zyxwvu-9" WHERE key = 3; -- Change just one row!
SELECT * FROM T1; -- Check the data...
Note that in this example table, the key column cannot be updated because
it is a serial column; if it were an integer column, it would be listed in
the trigger too:
CREATE TRIGGER u_t1 UPDATE OF key, data ON T1 ...
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>