RE: Insert triggers, is this a bug, is there a work around?

In article <35324736.4638FB94@callamer.com>, David Zepp <issac@callamer.com> writes: > I'd like to change a "LastUpdatedBy" column on a table in an insert and >update trigger, rather than from the application. > >Works with update, but not with insert (error -747). Documentation >backs this up, says you cannot reference the triggering table in a >triggered SQL statement, if the trigger EVENT is an INSERT. Did >Informix cripple their triggers like this for a reason? Has anyone >found a clean way around this? (Other than using another DBMS) No, it isn't a bug. Yes, triggers were implemented this way for a reason. You may disagree with the reason, but there was a reason why it was implemented as it was. I know I've discussed this in the not too distant past, but the basic answer is No, there isn't a direct way around this problem. There is an indirect way around it, though, which is to ensure that there's a DEFAULT on the column(s), and then to omit the column(s) from the list in the INSERT statement (you do list all the columns you are inserting into, don't you?). For example: CREATE TABLE SomeTable ( RefNum SERIAL(123456) NOT NULL PRIMARY KEY, Information VARCHAR(255) NOT NULL, LastUpdatedBy CHAR(8) DEFAULT USER NOT NULL, LastUpdatedAt DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND NOTNULL ); CREATE TRIGGER upd_sometable UPDATE ON SomeTable REFERENCING OLD AS OLD FOR EACH ROW (UPDATE SomeTable SET (LastUpdatedBy, LastUpdatedAt) = (USER, CURRENT YEAR TO SECOND) WHERE RefNum = OLD.RefNum); INSERT INTO SomeTable(RefNum, Information) VALUES(0, 'This is the data'); SELECT * FROM SomeTable; !sleep 20 UPDATE SomeTable SET Information = 'Obsoleted by the passage of time' WHERE RefNum = 123456; SELECT * FROM SomeTable; Give or take any syntax errors (I haven't check this code on an actual database today) and line folding by the email system, the INSERT will create a row with the current user as the value in the LastUpdatedBy column, and the current time as the value in the LastUpdatedAt column; the first SELECT will show those values. The UPDATE will then change the Information field, and the trigger will change the LastpUdated* columns. If the INSERT explicitly set those columns, then nothing can override what the user/programmer requested; similarly, if the UPDATE specifically set them, the trigger could not update them. But, in the absence of direct instructions, the database can be made to do more or less what you want. The hard part is if you don't want USER in the LastUpdatedBy column at INSERT time; that is decidedly non-trivial to fix unless you want a fixed string instead. The reason why triggers are implemented as they are is to ensure that if the user requests that a particular value is inserted into the database, that value is inserted unless there is a check constraint or referential constraint which prohibits it - it gives precedence to the user over the writer of triggered actions. Yours, Jonathan Leffler (jleffler@visa.com) #include <bother.ms-exchange.h>