Trigger

Question

Post reply

Trigger

maijoe

SSC Enthusiast

Points: 140
More actions
December 5, 2003 at 4:28 pm

#83582

Hi all,
Please give me a hand.
I have a table like this
Customer_NameShip LocationShipper Default
BioTech WWT-TaiwanPortlinkYes
BioTech Chrontel-SJPortlinkYes
BioTech Chrontel-SJFed Ex No
How can I write an insert & update trigger so that I can add as many shippers to be used and switch between the Default status, but at one time only 1 shipper of the same ship location can be checked as Default.
Thanks a lot
Minh Vu

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

I would add a unique constraint on

(Customer_Name, Ship_Location, Shipper, Default)

One thing though instead of real names you may want to use foreign keys to look up tables for ship_location and Shipper and use customerID instead of the name. that willmake the constraint or index faster! and will be easier to maintain

HTH

* Noel

maijoe SSC Enthusiast Points: 140 More actions · Answer 2

I can not add [default] in the constrainst since it's a bit field. Also, I was asked to keep the current design. Therefore, I think of trigger, but still not figure out. Thanks for your ideas.

Minh Vu

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

If this is SQL Server 2000, I'd use a UDF with a constraint, but if you've got to use a trigger, perhaps something like this:



CREATE TRIGGER t_ui_CustShippers

ON CustShippers FOR INSERT, UPDATE AS

IF UPDATE([Default]) BEGIN

 IF EXISTS

 (SELECT *

  FROM CustShippers

  WHERE [Default] = 1

  GROUP BY Customer_Name, Ship_Location

  HAVING COUNT(*) > 1)

 BEGIN

  RAISERROR ('Only one location can be default', 16, 1)

  ROLLBACK

 END

END

--Jonathan

noeld SSC Guru Points: 96590 More actions · Answer 4

quote:
CREATE TRIGGER t_ui_CustShippers
ON CustShippers
FOR INSERT, UPDATE AS
IF UPDATE([Default])
BEGIN
IF EXISTS (
SELECT *
FROM CustShippers
WHERE [Default] = 1
GROUP BY Customer_Name, Ship_Location , Shipper
HAVING COUNT(*) > 1)
BEGIN
RAISERROR ('Only one location can be default', 16, 1)
ROLLBACK
END
END

I think one ship_location can accept more than one shipper

* Noel

Jonathan SSC-Insane Points: 20427 More actions · Answer 5

quote:
I think one ship_location can accept more than one shipper

That's what I assumed; but with only one of them being the default. Your two "answers" seem to be for a different question from that asked by Minh.

--Jonathan

noeld SSC Guru Points: 96590 More actions · Answer 6

quote:
Your two "answers" seem to be for a different question from that asked by Minh.

My recomendation was the constraint, YOURS was the trigger. I tried to make it look like the constraint though looking further into it, unsuccessfully .

YOUR answer will allow this records in the table:

BioTech Chrontel-SJ Fed Ex Yes

BioTech Chrontel-SJ Fed Ex No

I am not sure that's what Minh wants either

* Noel

Jonathan SSC-Insane Points: 20427 More actions · Answer 7

quote:
quote:
Your two "answers" seem to be for a different question from that asked by Minh.
My recomendation was the constraint, YOURS was the trigger. I tried to make it look like the constraint though looking further into it, unsuccessfully .
YOUR answer will allow this records in the table:
BioTech Chrontel-SJ Fed Ex Yes
BioTech Chrontel-SJ Fed Ex No
BioTech Chrontel-SJ Fed Ex No
BioTech Chrontel-SJ Fed Ex No
BioTech Chrontel-SJ Fed Ex No
I am not sure that's what Minh wants either

Please read the question again a few times. Her question has nothing to do with requiring unique values. I assume she's already got a constraint for that (e.g. (Customer_Name, Ship_Location, Shipper)).

Your constraint would disallow more than two shippers per location, and your revision to the trigger would allow more than one default per location.

--Jonathan

noeld SSC Guru Points: 96590 More actions · Answer 8

quote:
quote:
quote:
Your two "answers" seem to be for a different question from that asked by Minh.
My recomendation was the constraint, YOURS was the trigger. I tried to make it look like the constraint though looking further into it, unsuccessfully .
YOUR answer will allow this records in the table:
BioTech Chrontel-SJ Fed Ex Yes
BioTech Chrontel-SJ Fed Ex No
BioTech Chrontel-SJ Fed Ex No
BioTech Chrontel-SJ Fed Ex No
BioTech Chrontel-SJ Fed Ex No
I am not sure that's what Minh wants either
Please read the question again a few times. Her question has nothing to do with requiring unique values. I assume she's already got a constraint for that (e.g. (Customer_Name, Ship_Location, Shipper)).
Your constraint would disallow more than two shippers per location, and your revision to the trigger would allow more than one default per location.
--Jonathan

after reading the question for the 10th time I NOW see what you meant , must be too much multitasking overhere ( i must be tired)

sorry for the confusion.

* Noel

maijoe SSC Enthusiast Points: 140 More actions · Answer 9

Thanks for all of your helps.

Yes, I want one location can have multiple shipper, but only one will be a default at a time. Johnathan's trigger works perfectly for me.

Minh Vu