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

Leffler, Jonathan wrote: > 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 NOT> NULL > ); > > 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> Triggers, along with stored procedures, are often used to implement additional constraints not implementable using check clauses or referential constraints. In these situations, I believe the triggered actions should get the precedence. I'm surprised more people don't complain about this. Also, I was under the impression that - most of the time - people want insert triggers to provide values for columns they don't intend for the user to specify values for. This seems useful and doesn't violate the reason you specified above regarding giving the user precedence. Besides, this is how update triggers work - update triggers can only columns not specified by the user. Seems inconsistent to me and I think people do recognize this. I know there was a feature request (#2566) for insert triggers submitted back in 1993. Anyone wanting this feature should call Informix and add their name to the list for this feature request. Jonathan, any idea why informix seems so resistant to changing this? Roger Tomas AG Communication Systems