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:
[code=oracle]
CREATE OR REPLACE PROCEDURE p_check_lease_d ate (
p_lease_id Lease.LeaseID%T YPE,
p_property_id Lease.PropertyI D%TYPE,
p_room_id Lease.RoomID%TY PE,
p_start_date Lease.StartDate %TYPE,
p_end_date Lease.EndDate%T YPE,
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.PropertyI D = 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;[/code]

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

[code=oracle]
CREATE OR REPLACE TRIGGER trg_Double_Leas e_Error
BEFORE INSERT OR UPDATE OF EndDate ON Lease
FOR EACH ROW

BEGIN
call p_check_lease_d ate(:new.LeaseI D);

END;[/code]

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_d ate(:NEW.LeaseI D, :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.