Re: Trigger that acts on referencing table

>From: Lorrie Gagnon <lgagnon@sdd.hp.com> >Date: Wed, 13 Aug 1997 15:52:38 -0700 >X-Informix-List-Id: <news.41621> > >I'd like to incorporate the following into my table designs, but it >doesn't appear that Informix supports the capability: > >Table XYZ ( > <columns>... > modified_datetime datetime year to second >) > >Each time a row is inserted or updated, I would like to have a trigger >automatically update the modified_datetime so that I always know when >a row was last changed. > >Informix triggers don't seem to let me update the time in this table >on an insert (I can update the time in an update trigger if that column >is not referenced in the update). It's interesting that you've found the trick for UPDATE, which I think is harder than the trick for INSERT. >If anyone has needed to do this, how did you implement it? Is there a >better way to provide this sort of information about the row? What I do is: CREATE TABLE Xyz ( ... AddUser CHAR(8) DEFAULT USER NOT NULL, AddTime DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND NOT NULL, UpdUser CHAR(8) DEFAULT USER NOT NULL, UpdTime DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND NOT NULL ) The INSERT statement must not insert any value for the columns which are to be defaulted. One way of handling that is to do inserts through a projection view which omits these four columns. Your update trigger then simply changes the UpdUser and UpdTime columns (the AddUser and AddTime columns never change, of course). And always, but always, if the user explicitly specifies a value for the column, it overrides anything that the triggers or defaults do. An alternative technique has a separate table which is used to log the changes: CREATE TABLE XyzLog ( XYZPrimaryKey ... OpType CHAR(1) NOT NULL CHECK (OpType IN ('I', 'U', 'D')) CONSTRAINT C1_XyzLog, OpUser CHAR(8) NOT NULL, OpTime DATETIME YEAR TO SECOND NOT NULL ) and the triggers simply insert the appropriate values into this table. Since this table should itself have a primary key, you might want to add a serial column... Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: Warning I do not reply to messages with anti-spam in the return path.