By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,058 Members | 1,217 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,058 IT Pros & Developers. It's quick & easy.

Help with Triggers

P: n/a
I'm trying my hand at triggers and it doesn't seem to be working for me. I
have a very simple database that consists of one table: Employees. I want
to create a trigger that will limit the EMP_TITLE field to either Ms., Mr.,
or Mrs. I am using the following code:

CREATE trigger triTitleCheck
ON employee
FOR insert, update
AS
declare @v1 varchar
SELECT @v1 = inserted.emp_title FROM inserted

if @v1 <> 'Mr.' and @v1 <> 'Mrs.' and @v1 <> 'Ms.'

BEGIN
rollback transaction
raiserror( 'Title must be one of the following: Mr., Ms., or
Mrs.', 16, 10 )
END

It is generating the error for ANY data, even if I do enter one of the three
legal values. Any help?

-- Curtis

Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Thu, 4 Nov 2004 22:37:23 -0600, Curtis Gilchrist wrote:
I'm trying my hand at triggers and it doesn't seem to be working for me. I
have a very simple database that consists of one table: Employees. I want
to create a trigger that will limit the EMP_TITLE field to either Ms., Mr.,
or Mrs. I am using the following code:

CREATE trigger triTitleCheck
ON employee
FOR insert, update
AS
declare @v1 varchar
SELECT @v1 = inserted.emp_title FROM inserted

if @v1 <> 'Mr.' and @v1 <> 'Mrs.' and @v1 <> 'Ms.'

BEGIN
rollback transaction
raiserror( 'Title must be one of the following: Mr., Ms., or
Mrs.', 16, 10 )
END

It is generating the error for ANY data, even if I do enter one of the three
legal values. Any help?

-- Curtis


Hi Curtis,

First, limiting data to a set of legal values is not a task for a trigger.
You use a CHECK constraint if the set of legal values is fairly short and
stable, or a FOREIGN KEY constraint to a lookup table if the list is long
and need to be changed relatively often.

Examples, assuming the titles are stable and the list of valid department
is subject to change:

CREATE TABLE Employees (Emp_No int NOT NULL,
Title char(4) NOT NULL,
Dept_No int NOT NULL,
PRIMARY KEY (Emp_No),
CHECK (Title IN 'Mr.', 'Mrs.', 'Ms.'),
FOREIGN KEY (Dept_No) REFERENCES Departments
)
Now, assuming you attempt to use a trigger just to get some exercise,
there are two errors in your trigger code. The first is the declaration of
@v1. Since you didn't specify a length, SQL Server assumes varchar(1);
after the assignment
SELECT @v1 = inserted.emp_title FROM inserted
the value of @v1 will be truncated to 'M' if the row inserted has a legal
value. Since 'M' is not equal to 'Mr.', 'Mrs.' or 'Ms.', the transaction
is rolled back and the error is raised.

The last problem with your trigger is that it doesn't handle multi-row or
zero-row updates or inserts. Remember that a trigger is fired exactly once
per insert, update or delete statement; all rows affected by the trigger
will be in the inserted and/or deleted pseudo-tables. Your current trigger
will check just one of the rows (and it's hard to predict exactly which
one), so you'll still be able to load erroneous data! Also, your trigger
will raise an error (even after correcting the @v1 declaration) if you
write something like
UPDATE Employees
SET Title = 'Mr.'
WHERE Emp_No <> Emp_No
(which will of course affect no row at all)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2

P: n/a
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
Also, your trigger will raise an error (even after correcting the @v1
declaration) if you write something like
UPDATE Employees
SET Title = 'Mr.'
WHERE Emp_No <> Emp_No
(which will of course affect no row at all)


That would only cause an error to be raised if ANSI_NULLS is off, which
it shouldn't be.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

P: n/a
On Fri, 5 Nov 2004 22:41:37 +0000 (UTC), Erland Sommarskog wrote:
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
Also, your trigger will raise an error (even after correcting the @v1
declaration) if you write something like
UPDATE Employees
SET Title = 'Mr.'
WHERE Emp_No <> Emp_No
(which will of course affect no row at all)


That would only cause an error to be raised if ANSI_NULLS is off, which
it shouldn't be.


Hi Erland,

Woops! You're right. Thanks for the catch!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.