PL/SQL Trigger Restrictions

[Constantine AI]

I'm trying to use PL/SQL to create a trigger that will stop double booking entries. Based on a duration field and a start time.

 
Create or Replace Trigger LeaseChk
Before insert on Lease
 
Begin
 
IF :NEW.StartDate and :NEW.Duration >= :OLD.StartDate and NEW.Duration (Some sort of Between Date Function i think) 
Where :NEW.PropertyID and :NEW.RoomID = :OLD.PropertyID and :OLD.RoomID
Then
RAISE_APPLICATION_ERROR (-20500, 'There is already a Lease created for this Property and Room for this time');
End IF;
END;
/
 

Any help would greatly be appreciated!

[debasisdas]

What do u mean by double booking entries

write the syntax correctly.

check for some sample in the how to section of the forum.

[Constantine AI]

I have 3 criterias that need checking against; PropertyID, RoomID and StartDate with a calculated Duration. In terms of having a Lease on a Property, for example

TenantID PropertyID RoomID StartDate Duration(Weeks) EndDate
1 1 1 29/04/2008 10 08/07/2008
2 1 1 06/05/2008 12 29/07/2008

I would like a stored procedure / trigger to check the input of TenantID 2 before insertion and bringing up a message that the PropertyID and RoomID are booked out for their chosen Dates. I need it to calculate the date duration and see that the previous booking has already taken those date periods so therefore the insertion should not complete until they have chosen a different time frame or a different Property or Room. I have thought of Date_Between and using the :OLD and :NEW row syntax. But i can't figure out how to structure the BEGIN statement other than what my previous exaple looks like. Hope this explains my situation better! Thanks again.

[amitpatel66]

Your trigger is syntatically incorrect. You need to first correct all the syntax errors and check if your trigger functions correctly!!