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
10 1508
Yes, I think it is possible.
Please, provide table definitions and possible data sample.
There are my tables:
Cruise(CruiseNO, Name, Duration, Cost, Ports)
Booking(CruiseNO *, CustomerID*, VoyageID*, CabinNo, Class, Evaluation)
Customer (CustomerID, CustomerName, Gender, Telephonenumber, address)
Ship(ShipID, ShipName, Port, tonnage)
Voyage(Voyage_ID, Startdate, Starting_Location, 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
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.
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
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.
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.
If i was to move the columns as you have suggested, how could i go about creating a trigger for this business rule ?
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,Startdate) = 12)
BEGIN
RAISERROR ('Invalid Start Date for cruises 5 days long.', 16, 1)
END
END
[/PHP]
I will give this a try and ill get back to you
Ideally I see some changes in these two tables to make data more relational.
Before
[PHP]Cruise(CruiseNO, Name, Duration, Cost, Ports)
Voyage(Voyage_ID, Startdate, Starting_Location, ShipID*[/PHP]
After
[PHP]Cruise(CruiseNO, Name, Duration, Cost, Ports, ShipID)
Voyage(Voyage_ID, Startdate, Starting_Location, CruiseNO)[/PHP]
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Matik |
last post by:
Hello to all,
I have a small question.
I call the SP outer the DB. The procedure deletes some record in table
T1.
The table T1 has a trigger after delete.
This is very importand for me, that...
|
by: Scott CM |
last post by:
I have a multi-part question regarding trigger performance. First of
all, is there performance gain from issuing the following within a
trigger:
SELECT PrimaryKeyColumn FROM INSERTED
opposed...
|
by: Martin |
last post by:
Hello,
I'm new with triggers and I can not find any good example on how to
do the following:
I have two tables WO and PM with the following fields:
WO.WONUM, VARCHAR(10)
WO.PMNUM,...
|
by: Mary |
last post by:
We are developing a DB2 V7 z/OS application which uses a "trigger"
table containing numerous triggers - each of which is activated by an
UPDATE to a different column of this "trigger" table. When...
|
by: Dave Sisk |
last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table.
(Note this is an external trigger defined with the ADDPFTRG CL command, not
a SQL trigger defined with the CREATE...
|
by: JohnO |
last post by:
Thanks to Serge and MarkB for recent tips and suggestions.
Ive rolled together a few stored procedures to assist with creating
audit triggers automagically. Hope someone finds this as useful as...
|
by: deepdata |
last post by:
Hi,
I am creating a trigger in DB2 express version.
When i use the following syntax to create trigger
CREATE TRIGGER USER_PK_TRIGGER
BEFORE INSERT On users
REFERENCING NEW As N
FOR EACH...
|
by: Ots |
last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app.
I have an Audit trigger that logs changes to tables. I want to apply
this trigger to many different tables. It's the same trigger,...
|
by: Shane |
last post by:
I have been instructed to write a trigger that effectively acts as a foreign
key. The point (I think) is to get me used to writing triggers that dont
use the primary key(s)
I have created the...
|
by: tracy |
last post by:
Hi, I really need help.
I run this script and error message appeal as below:
drop trigger log_errors_trig;
drop trigger log_errors_trig
ERROR at line 1:
ORA04080: trigger 'LOG_ERRORS-TRIG'...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |