Re: Trigger Help Need Please

From: Klaus.Zeuch <Klaus.Zeuch_at_t-online.de>
Date: 2000/05/12
Message-ID: <8fhp6o$7j5$11$1@news.t-online.com>#1/1

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