By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,709 Members | 2,155 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,709 IT Pros & Developers. It's quick & easy.

Calling a Stored Procedure via a trigger

100+
P: 129
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:
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PROCEDURE p_check_lease_date (
  2.       p_lease_id           Lease.LeaseID%TYPE,
  3.       p_property_id       Lease.PropertyID%TYPE,      
  4.       p_room_id            Lease.RoomID%TYPE,    
  5.       p_start_date         Lease.StartDate%TYPE,      
  6.       p_end_date           Lease.EndDate%TYPE,               
  7.       p_error        OUT   VARCHAR2                   
  8.    )
  9.  
  10.    IS
  11.       v_no_of_rooms  NUMBER;                 
  12.  
  13.    BEGIN
  14.  
  15.  
  16.       IF p_start_date > p_end_date
  17.       THEN
  18.          p_error := 'End date cannot be earlier than start date';
  19.       END IF;
  20.  
  21.  
  22.       SELECT COUNT (*)
  23.         INTO v_no_of_rooms
  24.         FROM Lease
  25.  
  26.        WHERE (   (    p_start_date >= Lease.StartDate
  27.                   AND p_start_date <
  28.                                   NVL (Lease.EndDate, SYSDATE)
  29.                  )
  30.  
  31.               OR (    p_start_date <= Lease.StartDate
  32.                   AND p_end_date > Lease.StartDate
  33.                  )
  34.              )
  35.          AND Lease.RoomID = p_room_id
  36.      AND Lease.PropertyID = p_property_id
  37.          AND Lease.LeaseID != p_lease_id;                  
  38.  
  39.       IF v_no_of_rooms > 0
  40.       THEN
  41.          p_error := 'An Overlapping Room Booking Exists for these dates';
  42.       END IF;
  43.  
  44.      EXCEPTION
  45.       WHEN OTHERS
  46.       THEN
  47.          p_error :=
  48.                'System Error '
  49.             || SUBSTR (TO_CHAR (SQLCODE) || ' ' || SQLERRM, 1, 100);
  50.  
  51. END;
This is the code for our attempt of a trigger but it doesnt create properly:

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE TRIGGER trg_Double_Lease_Error
  2. BEFORE INSERT OR UPDATE OF EndDate ON Lease
  3. FOR EACH ROW
  4.  
  5. BEGIN
  6. call p_check_lease_date(:new.LeaseID); 
  7.  
  8. END;
Any help would be appreciated.
Apr 1 '08 #1
Share this Question
Share on Google+
3 Replies


debasisdas
Expert 5K+
P: 8,127
Your procedure contains 6 pameters but you are passing only one to call it in trigger.
Apr 2 '08 #2

100+
P: 129
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?
Apr 2 '08 #3

debasisdas
Expert 5K+
P: 8,127
Out parameter is used to return value . You can't pass any value to that.
Apr 3 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.