Re: Question on triggers

>From: voorst@dordt.edu (Dale Van Voorst) >Subject: Question on triggers >Date: 18 Jul 1994 21:46:11 GMT >X-Informix-List-Id: <news.7654> > >Two questions regarding triggers: > >1) Do I have to special order the book on triggers? I'm quite sure that >it didn't come with my engine or tools manuals. It should, I think, have come as a separate booklet with the 5.01 Engines. >2) Since I currently have no book, can someone tell me if the following >idea is applicable to the use of triggers. > >I want to keep track of the date of the last change to each row in a >table. I'd like to avoid programming everything in 4gl just to make >sure that the date always gets updated. It seems that triggers might >be just the ticket. If I could automatically trigger an update of the >date field whenever a row is updated, my life would be much easier. > >Is this a feasible use for a trigger? If not, any suggestions would >be appreciated. Yes, it is feasible. Given the table: CREATE TABLE 'wtsdba'.SecAction ( ActionID CHAR(12) NOT NULL PRIMARY KEY CONSTRAINT 'wtsdba'.PK_SecAction, Description VARCHAR(60) NOT NULL , Upd_User CHAR(8) DEFAULT USER NOT NULL, Upd_Time DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND NOT NULL ); You can create a trigger: CREATE TRIGGER 'wtsdba'.U2_SecAction UPDATE OF Description ON 'wtsdba'.SecAction REFERENCING NEW AS action FOR EACH ROW (UPDATE SecAction SET Upd_User = USER, Upd_Time = CURRENT YEAR TO SECOND WHERE ActionID = action.ActionID ); I used a second trigger to prevent updates to ActionID: CREATE TRIGGER 'wtsdba'.U1_SecAction UPDATE OF ActionID ON 'wtsdba'.SecAction BEFORE (EXECUTE PROCEDURE wtsdba_upd()); Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>