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

Jonathan Leffler wrote: > 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. Thanks for all the input. I just assumed this would be possible with insert triggers. Maybe I'll read the docs first, next time! I also thought I could use this to update a variety of other fields. Like column A being a quantity, and B a price, Column C could be a balance, C=A*B. Perhaps this should be implemented in any stored proc., or code that inserts/updates A and B. I just thought triggers would be the cleanest place to put this logic. David Zepp