473,324 Members | 2,178 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

SQL Trigger

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
Mar 29 '07 #1
10 1508
iburyak
1,017 Expert 512MB
Yes, I think it is possible.
Please, provide table definitions and possible data sample.
Mar 29 '07 #2
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
Mar 29 '07 #3
iburyak
1,017 Expert 512MB
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.
Mar 29 '07 #4
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
Mar 29 '07 #5
iburyak
1,017 Expert 512MB
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.
Mar 29 '07 #6
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.
Mar 29 '07 #7
If i was to move the columns as you have suggested, how could i go about creating a trigger for this business rule ?
Mar 29 '07 #8
iburyak
1,017 Expert 512MB
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]
Mar 29 '07 #9
I will give this a try and ill get back to you
Mar 29 '07 #10
iburyak
1,017 Expert 512MB
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]
Mar 29 '07 #11

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

Similar topics

1
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...
6
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...
9
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,...
6
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...
0
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...
0
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...
1
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...
9
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,...
7
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...
11
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'...
0
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...
0
isladogs
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...
0
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...
0
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...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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
0
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...
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.