Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 trigger

Author  Topic 

charlie82
Starting Member

5 Posts
Posted - 2007-09-27 : 02:13:45
Hi All,

I need help very badly and ASAP.

I have one table name customer. I need to know how to write a trigger named AddOrder to ensure that the customer number entered for
an order matches a customer number in the Customer table whenever a row in the Orders table is updated or inserted. If the customer name does not match, it will prompt a error message.


Thanks,
Charlie

Kristen
Test

22859 Posts
Posted - 2007-09-27 : 02:23:05
[code]
CREATE TRIGGER [AddOrder]
ON dbo.[Orders]
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
IF EXISTS
(
SELECT *
FROM inserted AS I
LEFT OUTER JOIN [Customer] AS C
ON I.[customer number] = C.[customer number]
WHERE C.[customer number]
)
BEGIN
ROLLBACK
RAISERROR ('Customer does not exist', 16, 1)
END
GO
[/code]
"order matches a customer number in the Customer table whenever a row in the Orders table is updated or inserted. If the customer name does not match"

I presume you meant NUMBER for the second reference?

Edit: Missed a bit!

Kristen
Go to Top of Page

charlie82
Starting Member

5 Posts
Posted - 2007-09-27 : 02:41:46
Hi Kristen,
Yes, Should be Customer number. Thanks!. Can i write the SQL as shown below? Is that correct?


CREATE AddOrder TRIGGER ON ORDER
FOR INSERT, UPDATE
AS
IF NOT EXIST
(SELECT * FROM CUSTOMER
WHERE CustomerNum = new:CustomerNum
)
/* Insert the new record into the Order table */
new:customerNumEntered = new:customerNum;

ELSE

/* Throw error that the customer number of the new order cannot be found in the customer table */
RAISERROR ('Customer does not exist', 16, 1)

END;
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-27 : 02:53:41
"Can i write the SQL as shown below? Is that correct?"

Doesn't look right to me!

What's the "new:" stuff doing?

Also a trigger must be built to handle multiple rows being inserted/updated at once; you code looks to be working on t he basis that there is only one row.

The "inserted" table in my example above holds the data for ALL the rows being inserted, or updated, and thus needs to be processed as a set, rather than as a single row.

Kristen
Go to Top of Page

charlie82
Starting Member

5 Posts
Posted - 2007-09-27 : 02:57:45
Hi Kristen,

The new is to insert a new record if the customer number matched. What i want is something like that.


This trigger will activated when a new Order record is inserted or updated, and it will check if the CustomerNum of the new Order record exists in the Customer table. If it does, the new Order record will be inserted into the Order table. Else, an error will be thrown to the user that the customer number of the new order record cannot be found in the customer table.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-27 : 03:05:44
Seems to me that a plain ordinary foreign key contraint would fix this problem.

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

charlie82
Starting Member

5 Posts
Posted - 2007-09-27 : 03:22:46
Hi,

I need to write the SQL for /* Insert the new record into the Order table */ and /* Throw error that the customer number of the new order cannot be found in the customer table */ wih the example below.
And it should come out with "This trigger will activated when a new Order record is inserted or updated, and it will check if the CustomerNum of the new Order record exists in the Customer table. If it does, the new Order record will be inserted into the Order table. Else, an error will be thrown to the user that the customer number of the new order record cannot be found in the customer table."


CREATE AddOrder TRIGGER ON ORDER
FOR INSERT, UPDATE
AS
IF NOT EXIST
(SELECT * FROM CUSTOMER
WHERE CustomerNum = new:CustomerNum
)
/* Insert the new record into the Order table */

ELSE

/* Throw error that the customer number of the new order cannot be found in the customer table */

END;


Please help .... Thanks In advance ....
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-27 : 03:39:43
Triggers don't work like that, so if this is a home work assignment you'll have to find out what your teacher intended, because you certainly can't do "/* Insert the new record into the Order table */" with a standard "FOR" trigger.

Next time please say that you are asking for help with homework because we don't do your work for you, although we will help with whatever you are stuck on.

Kristen
Go to Top of Page

charlie82
Starting Member

5 Posts
Posted - 2007-09-27 : 03:52:28
Ops,

Okok ... thanks thanks
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts
Posted - 2007-09-27 : 04:34:31
ahh, homework assignment.

we need a homework forum.



-ec
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-09-27 : 04:39:22
quote:
Originally posted by eyechart

ahh, homework assignment.

we need a homework forum.

-ec


What's the diff ? The general rules still apply. We don't do homework

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-09-27 : 04:42:15
or unless you are thinking of a forum where students post their homework assignment and other students or beginners can take an attempt at that ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts
Posted - 2007-09-27 : 04:47:07
quote:
Originally posted by khtan

or unless you are thinking of a forum where students post their homework assignment and other students or beginners can take an attempt at that ?

KH
[spoiler]Time is always against us[/spoiler]




yeah, exactly. If we had a forum where we could help a bit instead of give answers outright. more of a learning forum than anything.

this idea comes up a lot. And i think that it continually gets shot down. Anyway, I think it would take the guesswork out of when people are asking homework questions.



-ec
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-27 : 04:49:25
A new forum named Homework?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-09-27 : 04:56:09
Assignment / Homework / Schoolwork

And also Interview Questions ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-27 : 05:33:00
Blimey, people are going to post in the right forum from now on? How cool is that ...
Go to Top of Page

johnsql
Posting Yak Master

161 Posts
Posted - 2007-09-27 : 12:26:14
quote:
Originally posted by Peso

Seems to me that a plain ordinary foreign key contraint would fix this problem.

E 12°55'05.25"
N 56°04'39.16"


The foreign/primary key relationship do not work if the 2 tables are designed in stand-alone way or people do not want to make such contraints when creating the tables, right?
Go to Top of Page

johnsql
Posting Yak Master

161 Posts
Posted - 2007-09-27 : 13:09:08
quote:
Originally posted by Kristen


CREATE TRIGGER [AddOrder]
ON dbo.[Orders]
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
IF EXISTS
(
    SELECT *
    FROM inserted AS I
         LEFT OUTER JOIN [Customer] AS C
             ON I.[customer number] = C.[customer number]
    WHERE C.[customer number]
)
BEGIN
    ROLLBACK
    RAISERROR ('Customer does not exist', 16, 1)
END
GO

"order matches a customer number in the Customer table whenever a row in the Orders table is updated or inserted. If the customer name does not match"

I presume you meant NUMBER for the second reference?

Edit: Missed a bit!

Kristen


---
I have a question here. Right after the statement "SET NOCOUNT ON", why don't you If NOT Exists(...) instead of If Exists(...) ?
Go to Top of Page

johnsql
Posting Yak Master

161 Posts
Posted - 2007-09-27 : 13:10:08
quote:
Originally posted by johnsql

quote:
Originally posted by Kristen


CREATE TRIGGER [AddOrder]
ON dbo.[Orders]
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
IF EXISTS
(
    SELECT *
    FROM inserted AS I
         LEFT OUTER JOIN [Customer] AS C
             ON I.[customer number] = C.[customer number]
    WHERE C.[customer number]
)
BEGIN
    ROLLBACK
    RAISERROR ('Customer does not exist', 16, 1)
END
GO

"order matches a customer number in the Customer table whenever a row in the Orders table is updated or inserted. If the customer name does not match"

I presume you meant NUMBER for the second reference?

Edit: Missed a bit!

Kristen


---
I have a question here. Right after the statement "SET NOCOUNT ON", why don't you If NOT Exists(...) instead of If Exists(...) ?



Sorry my editting error. "Why don't you use ...?"
Go to Top of Page

johnsql
Posting Yak Master

161 Posts
Posted - 2007-09-27 : 13:12:17
quote:
Originally posted by johnsql

quote:
Originally posted by johnsql

quote:
Originally posted by Kristen


CREATE TRIGGER [AddOrder]
ON dbo.[Orders]
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
IF EXISTS
(
    SELECT *
    FROM inserted AS I
         LEFT OUTER JOIN [Customer] AS C
             ON I.[customer number] = C.[customer number]
    WHERE C.[customer number]
)
BEGIN
    ROLLBACK
    RAISERROR ('Customer does not exist', 16, 1)
END
GO

"order matches a customer number in the Customer table whenever a row in the Orders table is updated or inserted. If the customer name does not match"

I presume you meant NUMBER for the second reference?

Edit: Missed a bit!

Kristen


---
I have a question here. Right after the statement "SET NOCOUNT ON", why don't you If NOT Exists(...) instead of If Exists(...) ?



Sorry my editting error. "Why don't you use ...?"


I think over here like this if I use IF NOT EXISTS(...), then I have to use INNER JOIN not LEFT JOIN, right?
Thanks.
Go to Top of Page

johnsql
Posting Yak Master

161 Posts
Posted - 2007-09-27 : 13:16:53
quote:
Originally posted by Kristen


CREATE TRIGGER [AddOrder]
ON dbo.[Orders]
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
IF EXISTS
(
    SELECT *
    FROM inserted AS I
         LEFT OUTER JOIN [Customer] AS C
             ON I.[customer number] = C.[customer number]
    WHERE C.[customer number]
)
BEGIN
    ROLLBACK
    RAISERROR ('Customer does not exist', 16, 1)
END
GO

"order matches a customer number in the Customer table whenever a row in the Orders table is updated or inserted. If the customer name does not match"

I presume you meant NUMBER for the second reference?

Edit: Missed a bit!

Kristen


Well, I tried to use your IF EXISTS ( ... LEFT JOIN ...). The trigger fails even when I tried to insert a row into table Orders and that row's cutomerID column matches an entry in table Customer !
So, I think we should use IF NOT EXISTS and an INNER JOIN for the trigger to work well.
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site