Trigger - Please help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Trigger - Please help

Author

Topic

mary H
Starting Member

32 Posts

Posted - 2008-11-17 : 03:36:02

Im creating an employee database for TAFE. I have an employee table with employee_id and place fields.

I need to add a trigger named employee_tg to the table. The trigger is activated by the Before Insert event.

When there's a duplication of employee_id and place the trigger generates a message. For example if a new inserted row has employee_id 20 place 6, and there is a row with employee_id 20 and place 6 already existed in the Employee table, the trigger generates a message: Duplicate Employee_id 20 Place 6.

The code I created is below:

[code]CREATE TRIGGER employee_tg
BEFORE INSERT ON employee
FOR EACH ROW

BEGIN

IF (empolyee_id!=NEW.employee_id)
THEN
INSERT INTO employee(employee_id,place)
VALUES (NEW.employee_id,NEW.place)

END IF;

IF (place!=NEW.place)
THEN
INSERT INTO employee(employee_id,place)
VALUES (NEW.employee_id,NEW.place)

END IF;[\code]

Does that look right? How do I display the error messages?

Any help would be appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 03:41:20

seeing your syntax i'm not sure you're using MS SQL SErver. if you are then below is way to do

CREATE TRIGGER employee_tg ON employee
INSTEAD OF INSERT 
AS

BEGIN

IF NOT EXISTS (SELECT 1 FROM employee e
           INNER JOIN INSERTED i
           ON i.employee_id=e.employee_id
           AND i.place=e.place)
INSERT INTO employee(employee_id,place)
SELECT employee_id,place
FROM INSERTED
ELSE
RAISEERROR ('Record with values already exists',10,1)--your error messaghe here
END

if you're not using ms sql server, post this in relevant forum. This is MS SQL Server forum.

mary H
Starting Member

32 Posts

Posted - 2008-11-17 : 03:47:46

Thank you visakh!

If there is a newly inserted employee_id and place that arent already presented in the table. How do I display the message ' No Duplication'?

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 04:11:46

quote:
Originally posted by mary H

Thank you visakh!

If there is a newly inserted employee_id and place that arent already presented in the table. How do I display the message ' No Duplication'?

Thanks again.

Not a good idea to display messages like this in trigger. why do you want to do this?

mary H
Starting Member

32 Posts

Posted - 2008-11-17 : 06:44:14

quote:
Originally posted by visakh16

quote:
Originally posted by mary H

Thank you visakh!

If there is a newly inserted employee_id and place that arent already presented in the table. How do I display the message ' No Duplication'?

Thanks again.

Not a good idea to display messages like this in trigger. why do you want to do this?

Hi, because its a TAFE assign so im required to display this message.
Your help would be appreciated..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 06:54:01

then make it a procedure rather than a trigger

mary H
Starting Member

32 Posts

Posted - 2008-11-17 : 23:58:41

quote:
Originally posted by visakh16

then make it a procedure rather than a trigger

I have to make a trigger and display both messages.

Any ideas on how to display the 'no duplication' message??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 00:05:57

quote:
Originally posted by mary H

quote:
Originally posted by visakh16

then make it a procedure rather than a trigger

I have to make a trigger and display both messages. Any ideas on how to display the 'no duplication' message??

where are you trying to display the message?

mary H
Starting Member

32 Posts

Posted - 2008-11-18 : 04:46:40

quote:

where are you trying to display the message?

Hi, As I stated above,
I need to add a trigger named employee_tg to the table. The trigger is activated by the Before Insert event.

When there's a duplication of employee_id and place the trigger generates a message. For example if a new inserted row has employee_id 20 place 6, and there is a row with employee_id 20 and place 6 already existed in the Employee table, the trigger generates a message: Duplicate Employee_id 20 Place 6.

However, if the newly inserted employee_id and place arent already in the Employee table, the trigger should generate a message: 'No Duplication'.

mary H
Starting Member

32 Posts

Posted - 2008-11-23 : 04:25:48

quote:

where are you trying to display the message?

Hi Visakh,

I was just wondering if your willing to help me as per above?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-11-23 : 06:36:57

It's called a unique constraint - there is no need for a trigger or any special code.
And you cannot "show" messages in SQL Server - you need to catch errors or return values and deal with them in the front end.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources