> >> Ratio: when deferred triggers on table A are used to calculate field > >> values of table B (which then obviously need an update), one might want > >> to prevent direct updates on these fields of table B [BW III] > It might also work for you to do this with access rights. Well, actually that's kinda how it's working at the moment, however I would rather avoid this additional dependancy on user/role since it's really field/situation-dependent (see below). > > The triggers can have different access rights than the user that > > caused the trigger to fire. Yep, and one can even get a bit more granularity switching roles. [TL] > Yes. Keying this off whether the triggers are deferred or not seems > a fairly bizarre choice --- that's surely not the primary property that > should determine what they can do to table B. Well, I didn't want to immediately bore the reader ;) The choice wasn't made lightely, though. The underlying reason is a performance enhancement. Basically, what's determining if the particular (!) fields in B can be updated is the fact whether or not this is done from a deferred trigger, as it is the sole place where this can be done efficiently. Table A is updated quite a lot within the same transaction and often the same rows are updated on different points in time. Table B is amongst other things, holding derivative information of table A, but this data is difficult to calculate, i.e. it takes time. A comparable situation would be that A is holding orderlines and B holds orders with values *that take long to calculate* because they depend on more rows of A than just the inserted/changed/deleted one. Or maybe even better, the derivative info in table B cannot be determined by solely using the inserted/updated/deleted data in A. Now, regular triggers obviously can take care of calculating the correct values for B upon each update of a row in A, but due to the amount of updates as well as the fact that a lot of the time rows in A are updated multiple times, this resulted in bad (at least, too bad) performance earlier. I solved this by using deferred triggers on A that are able to detect whether the corresponding row of B has already been updated, thus making sure the complex calculations are only done once. Now, table B is also in regular use by normal users, so they need normal write access and I'm keeping track of who's updating B when (your regular updated_on/by fields and history tables). > Also, it's quite unclear which part of the system you expect to enforce > the restriction against which other part. The suggestion as stated seemed > to be to let a trigger find out whether it itself is being fired in > a deferred fashion, but surely you don't want to trust the trigger to decide > whether it may update B. Ah, no, the trigger must not decide the latter, whether or not B as a whole may be updated solely depends (as it should) on the access rights of the user running/triggering the code. It's more like I'm trying to prevent direct updates of certain fields in B while knowing that the only way such update can be done efficiently is from within these deferred triggers. I can use a 'security definer' on the deferred triggers to allow switching roles, then switch role within them and check for that with 'current_user' in the regular triggers that will fire upon B's update, while still using 'session_user' to get to the original id that logged in. But as stated, given the efficiency-knowledge I'd rather know where I'm called from. I just wondered if there was a way to know the difference. Given the TG_WHEN var I could imagine there was, but If there is not and it's also not something expected to be 'nice to have', so be it, no problem ;) Too bad TriggerEvent doesn't seem to make this info available, either ;) -- Best, Frank.
