SQL Trigger

[Matti24]

I am fairly new to sql and as part of an assignment I have to create triggers to satisfy business rules.

The first business rule is:

ensure that cruises of five days duration cannot be run in December.

the two columns that are relevant to this trigger are in two separate tables and I am not sure if this will cause any problems.

If anyone can help or point me in the right direction then this would be great!

Thanks

[iburyak]

Yes, I think it is possible.
Please, provide table definitions and possible data sample.

[Matti24]

There are my tables:

Cruise(CruiseNO , Name, Duration, Cost, Ports)

Booking(CruiseN O *, CustomerID*, VoyageID*, CabinNo, Class, Evaluation)

Customer (CustomerID, CustomerName, Gender, Telephonenumber , address)

Ship(ShipID, ShipName, Port, tonnage)

Voyage(Voyage_I D, Startdate, Starting_Locati on, ShipID*


Here is some sample data from voyage

(1, '17-MAR-2007', 'Athens', 1)
(2, '20-APRIL-2006', 'Naples', 2)

Here is some sample data from cruise

(1, 'Dream Way' , 2, 600 , 'Naples');
(2, 'HotHotHot', 3 , 2000 , 'Blackpool');

Any help would be great, thanks for taking the time out

[iburyak]

I don't see relation between table Voyage and Cruise.

If I understand it correctly we have to check Duration in Cruise and if it is 5 then reject entree into Voyage with StartDate in December.

[Matti24]

I don't see relation between table Voyage and Cruise.

If I understand it correctly we have to check Duration in Cruise and if it is 5 then reject entree into Voyage with StartDate in December.

Sorry my bad, voyage and cruise are linked via the booking table

[iburyak]

If you would have additional column in Voyage which is CruiseNo it would work. Make sure CruiseNo is a unique value on a Cruise table.

[Matti24]

Sorry my bad, voyage and cruise are linked via the booking table

Ill build on that a little :) what has to happen is that if five is entered within the date of December then the entry is rejected, but I am not too sure how this can be done as the data is in seperate tables.

[Matti24]

If i was to move the columns as you have suggested, how could i go about creating a trigger for this business rule ?

[iburyak]

Column should not be moved it should be repeated in this table.

If link somehow will be established we can write trigger similar to this:

[PHP]CREATE TRIGGER tI_Voyage ON Voyage FOR INSERT AS

BEGIN

IF exists( SELECT *
FROM inserted i
JOIN Cruise c on i.CruiseNO = c.CruiseNO
WHERE c.Duration = 5
AND datepart(m,Star tdate) = 12)
BEGIN
RAISERROR ('Invalid Start Date for cruises 5 days long.', 16, 1)
END


END
[/PHP]

[Matti24]

I will give this a try and ill get back to you

[iburyak]

Ideally I see some changes in these two tables to make data more relational.

Before

[PHP]Cruise(CruiseNO , Name, Duration, Cost, Ports)

Voyage(Voyage_I D, Startdate, Starting_Locati on, ShipID*[/PHP]

After

[PHP]Cruise(CruiseNO , Name, Duration, Cost, Ports, ShipID)

Voyage(Voyage_I D, Startdate, Starting_Locati on, CruiseNO)[/PHP]