Trigger Question - 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 Question

Author

Topic

knight07
Starting Member

17 Posts

Posted - 2008-04-11 : 08:00:49

Hello everyone:
I'm new to this forum and to SQL server as well. My experience was mostly with Access. But any way, i've read a wonderful book entitled "SQL Server 2005 for developers" By Bryan Syverson and Joel Murach and I think I got some understanding with SQL server.
To make the story short, I have a question about building a trigger.
From what I understand, a trigger is used to enforce design rules and to make sure that data is managed correctly.
The trigger am thinking about will check the data in 2 columns in a certain table and compare them with data that is about to be added, and if they're already there, the newly submitted data is rejected.
First of all, I know where to add anew stored procedure and where to create functions, but have no idea where should I create the trigger in the management studio.
Second of all, does my idea seems right? and how can come about to do it? I'm sure it is a basic stuff to do, but I dont know how!
So please help...

By the way,I'm using SQL Serever 2005 Express edition.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-11 : 08:12:12

Why not place UNIQUE CONSTRAINT on those two columns instead?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

knight07
Starting Member

17 Posts

Posted - 2008-04-11 : 08:24:34

Thank u harsh_athalye 4 your reply..
I guess this also a good idea eventhough how to do a unique constraint on 2 columns altogather.

But in fact, the goal of the project I'm doing now is to experience with SQL server stuff, so I thought why not trying triggers..

I'll consider your idea, but if I can get help with triggers I will appreciate it..
Thanx again.

tomy74
Starting Member

32 Posts

Posted - 2008-04-11 : 08:40:54

Hi Guys,

you can apply unique constraint on that but sometimes triggers are much better,

knight07, Use the following steps to create triggers using SQL Management studio:
-expand sql server node
-expand databases node
-choose ur database
-expand tablse
-choose the table where u want to apply trigger on it-
-expand the selected table node
-find triggers folder and right click on it
-select new trigger
-new sql query zone will be created at the right

Now u can select the trigger action : INSERT,DELETE,...

Regards,

Tarek Ghazali
----------------------
SQL Server MVP
http://www.sqlmvp.com

knight07
Starting Member

17 Posts

Posted - 2008-04-13 : 01:15:09

Thanx tomy74.. and sorry for not replying sooner..I did'nt have Internet access for two days..

In fact, your reply has just what I was looking for..so, I really appreciate your help..

well, as a newbie to SQL server (with little background, just a little) I tried to create the trigger that will check 2 columns (combined) in the newly inserted record and compare them to what is already in the table. And if a duplicate of the combined columns appear, the insert is rejected.
This is my attempt:


CREATE TRIGGER trig_EvaluationCheck
ON tblEvaluations
	AFTER INSERT
As 
if Exists (Select bookID, evaluatorCivilID 
			FROM tblEvaluations
			WHERE bookID and evaluatorCivilID  
			IN (SELECT bookID, evaluatorCivivlID FROM Inserted))
Begin
	RAISERROR ('Evaluation has already been added!')
	RollBack TRAN
END

Thanx in advance, and thank you tomy74 once more

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-13 : 01:40:23

Listen to Harsh. this requirement is much better implemented by a unique constraint, not a trigger.

elsasoft.org

knight07
Starting Member

17 Posts

Posted - 2008-04-13 : 02:09:41

Thanx jezemine,
I appreciate your concern, and I dont mind trying the unique constraint if I have an idea how to apply it to both columns.
In fact, I already tried the trigger solution and I think it worked.
But I have no reason Not to try the other solution .. Any hint please?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-13 : 02:28:30

create unique index UQ_tblEvaluations on tblEvaluations(bookID, evaluatorCivivlID)

reasons to use index instead of trigger:

1. index is more efficient than trigger
2. besides enforcing the rule, index may also be used by the optimizer to make queries more efficient. triggers are useless to the optimizer in this regard.
3. from a modeling perspective, the constraint is cleaner. for example, any tools you are using to do ERD will be able to understand the constraint, but they would never figure out rules imposed by triggers.

elsasoft.org

knight07
Starting Member

17 Posts

Posted - 2008-04-13 : 02:44:47

That's cool jezemine.. it works very good.. Icant compare it to the trigger I tried , but I'll take your and harsh's word in this regard ..

Thanx so much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources