| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: an ON-COMMIT trigger desperately needed
I use developer 2000 to access a database back end so using object tables with nested table of records seems to be not an option. The idea to have another table with deferred check constraints is a cool one except that it still is not sufficient when it comes to more complicated examples like we are trying to overcome.
For instance it might not be only the number of rows in a child table that matter. In many situations depending on the type of the master record, certain fields in the child records are required and in addition there must be at least one row in the particular child table.
This makes the additional table and related check constraint and triggers on child tables quite complicated. In addition if there are a large number of rows in the master table as in our case then there is an additional equal number of rows in this additional table which leads to significant space loss since the data is really only relavent at the time of the transaction commit. One other drawback is that for each insert, update and delete I have to incur the performance overhead of an insert, update or delete into this table.
Back to the ultimate solution.
Is it possible that for tables with the key that I am interested in
I can put a trigger that inserts rows into a package table of records
with a list of the key values (eliminating duplicates).
And then every time some application fires a commit or a check
constraints then
1) a procedure fires
2) it traverses the table of records passing the key values as a
parameter to a procedure that reads the master rows and validates
the surrounding tables
3) if the validation succeeds then transaction rolls back
>
> Your example I believe was a deferred RI constraint.
> An example of a real world example would be:
> I have a table called CAR and a table called PARTS
> a car must have 4 wheels and a car must have a steering wheel.
> I want to make sure that no user other than the user actually doing
the
> inserts can see a partial car in the database. (i.e. defered
validation)
> At commit time if car does not have 4 wheels and a steering wheel
> transaction must error out and rollback.
> (similar to what happens below however the validation here is to
> select count(*) from PARTS where part type = wheel
> and if count != 4 then rollback
> and similar for sterring wheel)
>
I agree -- it would be functionality people would use, however.....
In this case, I would use an Oracle object type and create a complex
object
type CAR with a nested table|varray type of parts (or perhaps tires).
for
example:
ops$tkyte_at_8i> create or replace type parts_array as table of varchar2
(25)
2 /
Type created.
ops$tkyte_at_8i> create or replace type car as object
2 ( vin number, 3 parts parts_array4 )
ops$tkyte_at_8i> create table cars of car nested table parts store as
car_parts;
Table created.
ops$tkyte_at_8i> create or replace trigger car_trigger
2 after insert on cars for each row
3 declare
4 cnt number default 0;
5 begin
6 for i in 1 .. :new.parts.count loop 7 if :new.parts(i) = 'wheel' then 8 cnt := cnt+1; 9 end if; 10 end loop; 11 if (cnt<>4) then 12 raise_application_error( -20001, 'must have four wheels' ); 13 end if;
ops$tkyte_at_8i> insert into cars values ( car( 1234, parts_array( 'wheel',
'wheel', 'wheel', 'wheel' ) ) );
1 row created.
ops$tkyte_at_8i> insert into cars values ( car( 4321, parts_array( 'wheel',
'wheel', 'wheel' ) ) );
insert into cars values ( car( 4321, parts_array( 'wheel', 'wheel',
'wheel' ) ) )
*
ERROR at line 1:
ORA-20001: must have four wheels ORA-06512: at "OPS$TKYTE.CAR_TRIGGER", line 10 ORA-04088: error during execution of trigger 'OPS$TKYTE.CAR_TRIGGER
If I did not want to use object tables -- I would still use object
TYPES and
create an object view on my relational tables and use INSTEAD OF
triggers on
the view to:
o validate
o insert/update the base relational tables...
In this case, I assemble the car in one "row" if you will and can use a
conventional trigger. The client works with the "car", the database
works
with a "row" -- the row is really a complex set of rows in many
tables....
If I had to do this relationally -- knowing then need 4 tires, I have
four
columns - each of which is NOT NULL (deferrable if I want it). I would
just
store the data as it should be in a "relational" model.
If I had to do this in a "cars" and "parts" table -- I would maintain another table off to the side. It would be my "validation" table.
for example:
ops$tkyte_at_8i>
ops$tkyte_at_8i> create table cars ( vin number primary key );
Table created.
ops$tkyte_at_8i>
ops$tkyte_at_8i> create table parts ( vin number references cars, partname
varchar2(25) );
Table created.
ops$tkyte_at_8i>
ops$tkyte_at_8i> create table cars_parts_cnt
2 ( vin number,
3 partname varchar2(25),
4 cnt number,
5 constraint check_cnts check
6 ( ( partname = 'wheels' and cnt = 4 ) or
7 ( partname <> 'wheels' )
8 ) initially deferred
9 )
10 /
Table created.
ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace trigger t
2 after insert or update or delete on parts
3 for each row
4 begin
5 if (updating or inserting) 6 then 7 update cars_parts_cnt 8 set cnt=cnt+1 9 where vin = :new.vin 10 and partname=:new.partname; 11 12 if (sql%rowcount=0) then 13 insert into cars_parts_cnt values(:new.vin,:new.partname,1); 14 end if; 15 end if; 16 17 if (updating or deleting) 18 then 19 update cars_parts_cnt 20 set cnt=cnt-1 21 where vin = :old.vin 22 and partname=:old.partname; 23 end if;
Trigger created.
ops$tkyte_at_8i> ops$tkyte_at_8i> ops$tkyte_at_8i> insert into cars values ( 1234 );
1 row created.
ops$tkyte_at_8i> insert into parts values ( 1234, 'wheels' );
1 row created.
ops$tkyte_at_8i> insert into parts values ( 1234, 'wheels' );
1 row created.
ops$tkyte_at_8i> insert into parts values ( 1234, 'wheels' );
1 row created.
ops$tkyte_at_8i> insert into parts values ( 1234, 'wheels' );
1 row created.
ops$tkyte_at_8i> commit;
Commit complete.
ops$tkyte_at_8i> insert into cars values ( 4321 );
1 row created.
ops$tkyte_at_8i> insert into parts values ( 4321, 'wheels' );
1 row created.
ops$tkyte_at_8i> insert into parts values ( 4321, 'wheels' );
1 row created.
ops$tkyte_at_8i> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (OPS$TKYTE.CHECK_CNTS) violated
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Jul 06 2000 - 00:00:00 CDT
 |
 |