Using a Trigger instead of a Constraint to deny Dupilcates

[JessicaStevens]

Hello,

My name is Jessica and i am currently employed as a 1st Level Help Desk operator at Sunlight Technologies. We mainly service employee's who require basic assitance with operating their PC's and software within the company.

I have been working here for 4 years now, and the company has recently hired a new employee who has already started hassling me with how much he knows. I am not sure if this is because i am a woman and *THINKS* he can gain the respect of others in the workplace by undermining my IT experience, i dont know.

Anyway, forgive me i had to vent L-O-L

The purpose of this forum post was to show him up with an argument we had earlier. I KNOW i am right and its frustrating when you forget the basics thinking "This is pointless" when in college, and 4 years later you need that knowledge ha-ha.

I recall my professor teaching us, a way to use a MYSQL command to prompt a message when the operator inserts DUPILCATE data into a table. However nowaday's we would use a CONSTRAINT which i understand, although the new employee was arguing that it can NOT be done. I KNOW it can be done, although have lost the ability to recall HOW to.

The table and data is not really important, so if we use this random table:

TABLE NAME - FLOWERS
(Flower_ID, Flower_Name, Amount_Picked)
1, Daisy, 3
2, Pansy, 3
3, Poppy, 1
4, Sunflower, 9
5, Lavender, 6

How would i write a code that will display a message when an operator inserts say: INSERT INTO flowers (flower_id,flower_name) values (1,Daisy); ?

What am i doing wrong?

DELIMITER //
CREATE TRIGGER Flower_tg BEFORE INSERT ON flower FOR EACH ROW

BEGIN IF new.flower_id = flower_id
AND new.flower_name = flower_name
THEN
SET @message = 'This is a NO NO';

ELSEIF new.flower_id = new.flower_id
AND new.flower_name = new.flower_name
THEN
SET @message = 'Permission Granted';
END IF; END;//

Any assitance would be amazing and good karma to you and yours

 

[r937]

why not just use a PRIMARY KEY or UNIQUE constraint instead of re-inventing the wheel



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[DonQuichote]

How do you want to display the message? MySQL itself is a server. It does not do fancy popups or something like that. So just add a unique index to the table (which may consists of more than one field, in that case the combination must be unique).

But if you don't trap for errors, there will be no message. But there will be no message either if you use a trigger to do that.

By the way, adding an index really is the most elegant solution. A procedure or a trigger will just cause a failure. A unique index enables you use INSERT IGNORE, INSERT ... ON DUPLICATE KEY ... and that kind of constructs. Also, unique indexes are by far safer and clearer than triggers.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[mbalent]

Come on guys... What she wants is a way to show up the dweeb at work that is giving her a hard time and not to debate indexes and db design. The real issue is that mySql apparently does not have a 'RAISEERROR' method to allow for custom error reporting from inside triggers.

Matt

"Nature forges everything on the anvil of time

 

[ingresman]

With all due respect that's not why we are here, to show people up. If the guys a dweeb than he'll show himself up sooner or later.
Also I don't think my karma would be helped out by helping Jessica out.
sorry but that's the way I feel.