Re: Trigger Help Need Please

...error - in the statement-level trigger we have to clear the pl/sql-table before checking the number of bookings;

mypackage.v_wantstobook := mypackage.v_clear; if v_count >1 then
...

Sorry

Klaus

"Klaus.Zeuch" <Klaus.Zeuch_at_t-online.de> schrieb im Newsbeitrag news:8fhp6o$7j5$11$1_at_news.t-online.com...
> 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
>
> -- record:
> type myrecord is record (
> b_from date,
> b_to date);
> -- declare the pl/sql table
> type mytable is table of myrecord index by binary_integer;
> -- declare a variable of the pl/sql-table type
>
> 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;
> begin
> -- retrieve the id from the pl/sql table
> 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;
> if v_count >1 then
> -- >1 because the row-level trigger makes an insert!
>
> 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