Calling a Stored Procedure via a trigger

[Constantine AI]

Hi we have created a stored procedure to check the dates entered into a lease table does not overlap dates already stored for a lease. However when inserting overlapping lease dates, it allows us to insert this entry into the lease table, we was thinking of doing a trigger to execute the stored procedure to prevent a row being inserted into the lease table if there are overlapping dates:

The code we have for stored procedure is:

CREATE OR REPLACE PROCEDURE p_check_lease_date (
      p_lease_id           Lease.LeaseID%TYPE,
      p_property_id       Lease.PropertyID%TYPE,      
      p_room_id            Lease.RoomID%TYPE,    
      p_start_date         Lease.StartDate%TYPE,      
      p_end_date           Lease.EndDate%TYPE,               
      p_error        OUT   VARCHAR2                   
   )
 
   IS
      v_no_of_rooms  NUMBER;                 
 
   BEGIN
 
 
      IF p_start_date > p_end_date
      THEN
         p_error := 'End date cannot be earlier than start date';
      END IF;
 
 
      SELECT COUNT (*)
        INTO v_no_of_rooms
        FROM Lease
 
       WHERE (   (    p_start_date >= Lease.StartDate
                  AND p_start_date <
                                  NVL (Lease.EndDate, SYSDATE)
                 )
 
              OR (    p_start_date <= Lease.StartDate
                  AND p_end_date > Lease.StartDate
                 )
             )
         AND Lease.RoomID = p_room_id
     AND Lease.PropertyID = p_property_id
         AND Lease.LeaseID != p_lease_id;                  
 
      IF v_no_of_rooms > 0
      THEN
         p_error := 'An Overlapping Room Booking Exists for these dates';
      END IF;
 
     EXCEPTION
      WHEN OTHERS
      THEN
         p_error :=
               'System Error '
            || SUBSTR (TO_CHAR (SQLCODE) || ' ' || SQLERRM, 1, 100);
 
END;

This is the code for our attempt of a trigger but it doesnt create properly:

CREATE OR REPLACE TRIGGER trg_Double_Lease_Error
BEFORE INSERT OR UPDATE OF EndDate ON Lease
FOR EACH ROW
 
BEGIN
call p_check_lease_date(:new.LeaseID); 
 
END;

Any help would be appreciated.

[debasisdas]

Your procedure contains 6 pameters but you are passing only one to call it in trigger.

[Constantine AI]

Your procedure contains 6 pameters but you are passing only one to call it in trigger.

We have altered the code to this:

CALL p_check_lease_date(:NEW.LeaseID, :NEWPropertyID, :NEWRoomID, :NEWStartDate, :NEWEndDate, :NEW:p_error );

but it doesnt like the OUT parameter in the line of this trigger and doesnt like it if we take it out either, do you know how we should show the OUT parameter in this line?

[debasisdas]

Out parameter is used to return value . You can't pass any value to that.