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

Roger Tomas wrote: > 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 [...omitted...] > > 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 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. I should probably have prefixed the above with 'IMO', though that is always implicitly present in news group discussions. > Triggers, along with stored procedures, are often used to implement > additional constraints not implementable using check clauses or > referential constraints. Maybe that is more an issue of CHECK constraints need to be made more sophisticated. Has anybody checked whether you can execute a stored procedure in a CHECK clause? I suspect not, but you should be able to do so. > In these situations, I believe the triggered actions should get > the precedence. Maybe; I'm not 100% convinced. If I specify a value to be inserted that is not acceptable, then the INSERT should be rejected; if I specify a value which is acceptable, I don't want it fiddled with. If it needs fiddling with, it wasn't acceptable and should (perhaps) have been rejected. I can see that accepting a mixed-case value and converting to upper case to improve searching is a borderline case, but maybe the answer should be that the INSERT privileges should be denied except via a stored procedure which (a) fixes up the data and (b) inserts it into the database. > I'm surprised more people don't complain about this. Me too. > 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. If the user's should not be providing values for some column, then you should be seriously considering using views or stored procedures to prevent people inserting values in the columns. The options for default values are woefully inadequate (USER, SITENAME, CURRENT, TODAY and literals, I think; certainly not many other options, not even expressions like TODAY - 7). That needs fixing! > This seems useful and doesn't violate the reason you specified above > regarding giving the user precedence. I think it does violate the reason I gave. It may be useful, but it does violate my rights to have the data I requested the database to store actually stored in the database. > Besides, this is how update triggers work - update triggers can > only [modify] columns not specified by the user. So, UPDATE triggers can only affect data I didn't explicitly update. Similarly, an INSERT trigger should only be able to affect data I didn't explicitly INSERT. That translates to supplying default values for the columns not named in the INSERT statement. > Seems inconsistent to me and I think people do recognize this. I not yet convinced that your argument is consistent either. > 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. And the entry will be duly ignored. The FR database is a black hole, and no useful information escapes from a black hole (except, perhaps, randomly due to quantum effects, but remember that quanta tend to be rather small). One idea behind triggers was to be able to do things like cascading deletes in the days before cascading deletes, or to trigger actions such as replication of changes in the days before CDR (or whatever the acronym for replication is this week). The actions would occur on other tables. Doing things to the 'current' table was not, in my view, one of the intended used. That should be handled separately. I'm certainly not suggesting there isn't room for improvement -- there is. But I'm very far from convinced that the INSERT trigger is what needs fixing. > Jonathan, any idea why informix seems so resistant to changing this? No, and it isn't anything to do with me -- I've never been consulted on anything so useful. Yours, Jonathan Leffler (j.leffler@acm.org) #include <JustMyOpinion.h> PS: I'm mildly surprised to find how strong a stance I've been prodded into taking on this -- I half agree that you should be able to do more to control data during an insert. But I'm not yet convinced that the correct mechanism is the INSERT trigger (regardless of what O does). I think it should be handled by better defaulting, and/or access control via (DBA-privileged?) stored procedures which do the necessary work. Whether you'll ever get a coherent counter-argument parallel to mine from Informix is highly debatable, of course.