＋设为首页 ＋添加到收藏夹
在论坛中经常遇到变异表的问题,我收藏了一片有关变异表问题的文章,现在把他放在这里,希望能与大家分享!
avoiding mutating tables 【相关文章:上传图片文件，生成缩略图，并写上版权信息】
【扩展阅读:比尔盖茨的11点忠告】
【扩展信息:关于SQL SERVER的一些安全问题】 ok, so you´ve just recieved the error: ora-04091: table xxxx is mutating, trigger/function may not see itand you want to get around that. this short article will describe and demonstrate the various methods of getting around the mutating table error.
if you are interested in why you are getting it and in what cases you will get it, please see the oracle server application developers guide (click here to read it right now -- this link is to technet.oracle.com. you need a password to access this site but you can get one right away for free).
avoiding the mutating table error is fairly easy. we must defer processing against the mutating or constrainng table until an after trigger. we will consider two cases:
hitting the ora-4091 in an insert trigger or an update trigger where you only need access to the :new values
hitting the ora-4091 in a delete trigger or an update trigger where you need to access the :old values case 1 - you only need to access the :new values this case is the simplest. what we will do is capture the rowids of the inserted or udpated rows. we can then use these rowids in an after trigger to query up the affected rows. it always takes 3 triggers to work around the mutating table error. they are:a before trigger to set the package state to a known, consistent state
an after, row level trigger to capture each rows changes an after trigger to actually process the change. as an example -- to show how to do this, we will attempt to answer the following question: i have a table containing a key/status/effective date combination. when status changes, the values are propagated by trigger to a log table recording the ... 下一页