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@(protected)> wrote: >=20 > Tom, >=20 > That's an ingenious method, but it doesn't enforce the rule as stated (at= least as I understood it). >=20 > With your method, it would be possible to have 6 user authorities and 0 c= hange authorities, which doesn't jive with what he was saying. (There can = be at most 3 of each type for the database schema.) >=20 > Splitting Table B into two tables, B-change and B-user, plus your techniq= ue with a check constraint with values from 1 to 3 would work. >=20 > Jeff wrote to me and confirmed the reason he was considering using a shad= 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 > This technique uses 3 triggers and one package. >=20 > The package holds an array (pl/sql table) composed of the table b keys fo= r the table b records that are inserted or updated. >=20 > A pre-statement on insert and update trigger, to empty out the array. >=20 > A post-row on insert and update trigger, to add the record key to the arr= ay. (Updates only need to add the record key if the value of either of the= two flags was changed. Inserts only need to add it if either of the flags= is "Y". >=20 > A post-statement trigger on insert and update, that loops thru the array = and checks the count of records for each of the two flags. It should empty= the array and raise an application error if the rule is violated. If not,= it just empties the array. >=20 > The array could be made to just hold the foreign key values to table a in= stead of the keys to table b. > Whether that is done or not depends upon whether it would be faster to ch= 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 > Enjoy! >=20 > -- --Original Message-- -- >=20 > Have you thought of something as simple as an additional column with a > check constraint of values 1 thru 6? And then make this a part of the > primary key of the child table: >=20 > Table B > PK DB_NAME > PK SCHEMA_NAME > PK RECORD_NUMBER <=3D3D=3D3D=3D3D values of 1 thru 6 only. > USR_ID -- user (authority) > AUTH_INDICATOR -- change authority > USR_INDICATOR -- user authority >=20 > Simple but effective. >=20 > -- > http://www.freelists.org/webpage/oracle-l >=20
