Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Trigger Help Need Please
Hi,
if you just want to reject an insert for an id, that already has a booking in this time-range, you could do it with two triggers: One statement trigger and one row trigger. If you want to accomplish this with 1 statement trigger you'll run into ORA-04091 mutating table error.
Let's assume table booking consists of column bookerid (id of person who is booking) and the date-columns booking_from and booking_to.
[create table booking(bookerid number, booking_from date, booking_to date);]
In the row level trigger we just store the values for bookerid, from and to in a pl/sql table
The statement-trigger then checks if this user already has bookings in that period. If true an error is raised
The pl/sql table must be declared in an additional package.
Let's start with creating the package:
create or replace package mypackage as
-- pl/sql-Tables consist of two columns: Key-column and 1 value column
-- as we want to store bookerid as value of key-column and from and to
information
-- we have to create a record and make this record the value column of the
pl/sql-table
v_wantstobook mytable;
-- another variable to clear the pl/sql-table
v_clear mytable;
end mypackage;
now the row-level trigger - we just record the values of the insert-statement
create or replace trigger rec_ins
before insert or update on booking
for each row
begin
-- fill the pl/sql-table
mypackage.v_wantstobook(:new.bookerid).b_from := :new.booking_from; mypackage.v_wantstobook(:new.bookerid).b_to := :new.booking_to; end rec_ins;
the statement-level trigger
create or replace trigger rec_Stmt
after insert or update on booking
declare
v_count number := -1; v_id booking.bookerid%type; v_from booking.booking_from%type; v_to booking.booking_to%type; v_num binary_integer := 0;
v_num := mypackage.v_wantstobook.first; v_id := v_num; v_from := mypackage.v_wantstobook(v_num).b_from; v_to := mypackage.v_wantstobook(v_num).b_to;-- check if there's a booking
select count(*)
into v_count
from booking
where bookerid = v_id and booking_from <= v_to and booking_to >= v_from;
raise_application_error(-20000,'user ' || v_id || ' has already bookings in the range ' || to_char(v_from,'DDMMYYYY') || ' - ' || to_char(v_to,'DDMMYYYY'));
end if;
-- clear the pl/sql-table
mypackage.v_wantstobook := mypackage.v_clear;
end;
Hth
Klaus
"SB" <Nahhh_at_Way.Com> schrieb im Newsbeitrag
news:7lUS4.15$yY3.429_at_news11-gui.server.ntli.net...
> Im writing a row trigger at the moment which will fire before an insert on
a
> table. It will check that no double bookings will occur. My aim is to
read
> all the rows into a temporary table then use a cursor on that temporary
> table to scan through the rows and check that the to and from dates on the
> record to be inserted do not overlap with any dates currently in the
table.
> Then I need to delete all the records in the temporary table. I thought
> about writting three triggers i.e. one to insert, one to scan with the
> cursor, one to delete. But I now find that this is impossible as there is
> no way to order triggers and this method relies on pre-ordering. So I
need
> to combine the triggers but how can this be achieved within the scope of
the
> trigger? I need to scan with the cursor after the insert and then loop
for
> each row and delete then :
>
> CREATE OR REPLACE TRIGGER sercure_booking
> BEFORE INSERT OR UPDATE ON booking
> FOR EACH ROW
> BEGIN
> IF INSERT THEN
> INSERT INTO TEMP_TABLE( table variable names go here)
> VALUES( table variable names go here)
> ELSEIF UPDATE
> same as insert
> END-IF;
> END;
> /
>
> Where would I place the code for my cursor and deletion activities?
> This is all strange to me. I am just a humble COBOL programmer.
> I can also be emailed at paul.hargreaves1_at_net.ntl.com
> Many Thanks
> Paul
>
>
Received on Fri May 12 2000 - 00:00:00 CDT
![]() |
![]() |