Help with Triggers

[Curtis Gilchrist]

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_ti tle 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

[Hugo Kornelis]

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_ti tle 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_ti tle 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)

[Erland Sommarskog]

Hugo Kornelis (hugo@pe_NO_rFa ct.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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Hugo Kornelis]

On Fri, 5 Nov 2004 22:41:37 +0000 (UTC), Erland Sommarskog wrote:

Hugo Kornelis (hugo@pe_NO_rFa ct.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)