Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Data modeling question
When you're doing "cross-table" validation with triggers you do need
to be careful with your locking. Otherwise two outstanding
transactions (neither of which can see each others changes) might
commit and give you a data corruption. In the example above, we might
have 5 rows in the table and two sessions each with an pending insert.
Each session sees that we have not hit the limit of 6 and so the
commit is allowed... Voila! You've got 7 records.
Declarative options such as Jared's are generally better - even with this care is needed. For example, if we run Jared's script in session 1, then the following SQL in session 2 will (correctly) block:
insert into authorizations(database_id,schema_id,user_id,auth_type_id,auth_=
limit)
values(1,2,3,2,3);
because the validity of inserting this row depends on whether the first session commits or rolls back. So the declarative stuff guarantees your data will be OK, but if you want to be able to "look in advance" whether the insert will block, then once again you're up for some more complicated code to handle the locking
hth
connor
On 5/11/05, david wendelken <davewendelken_at_earthlink.net> wrote:
>=20
ow table was to avoid a mutating table error caused by querying table b whi= lst inserting or updating table b. If Jeff decides to go with a single ta= ble and triggers to enforce the rule (instead of a shadow table and trigger=s), here's one way to go about it.
>=20
>=20
eck for duplicates in the array than it would be to loop thru each row at t= he end, and upon whether there are other post-statement rules on table b to= deal with. This optimization technique works really well with single colu= mn numeric primery keys, as the key value can be the array index value also=.
>=20
>=20
>=20
>=20
>=20
>=20
--=20
Connor McDonald
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
email: connor_mcdonald_at_yahoo.com
web: http://www.oracledba.co.uk
"Semper in excremento, sole profundum qui variat"
-- http://www.freelists.org/webpage/oracle-lReceived on Wed May 11 2005 - 20:35:38 CDT
![]() |
![]() |