I am working on a trigger for a class project.
I have a table named salesman
with a column named type
. All employees in the table currently have a type
of full
, part
or temp
.
The purpose of the trigger is to check a newly inserted row’s type
and make sure that it matches one of the three that already exist. If it does it should allow the insert, if not it should stop it or roll it back. Thank you for any ideas that you may have!
This is the latest permutation of the code.
CREATE TRIGGER trg_validate_emp_type ON salesman FOR INSERT AS BEGIN DECLARE @job varchar (20) SELECT @job = (SELECT type FROM inserted) IF NOT EXISTS (SELECT 1 FROM salesman WHERE UPPER(type) = UPPER(@job)) BEGIN ROLLBACK TRANSACTION PRINT 'employee type not valid' END END
Advertisement
Answer
As several people pointed out, this looks like a job for a check constraint or a foreign key.
eg
create table salesman(id int primary key, type varchar(20) check (type in ('a','b')))
or
create table salesman_type (type varchar(20) primary key ) create table salesman(id int primary key, type varchar(20) references salesman_type)
But if you really did want to write a trigger that prevented the insertion of values that didn’t already exist in the table, you could do it like this:
use tempdb go create table salesman ( id int identity primary key, type varchar(20) not null, index ix_salesman_type(type) ) insert into salesman(id,type) values (1,'a') insert into salesman(id,type) values (2,'b') go CREATE OR ALTER TRIGGER trg_validate_emp_type ON salesman FOR INSERT AS BEGIN IF EXISTS ( SELECT * FROM inserted WHERE type not in ( select type from salesman where id not in (select id from inserted) ) ) BEGIN throw 50001, 'employee type not valid', 1; END END go insert into salesman(id,type) values (3,'c') -- fails insert into salesman(id,type) values (3,'a') -- succeeds
Since XACT_ABORT defaults to ON in a trigger, you can simply THROW instead of ROLLBACK + PRINT, and the client gets a better error message.