Skip to content
Advertisement

Validation trigger not working in SQL Server

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.

Advertisement

Answer

As several people pointed out, this looks like a job for a check constraint or a foreign key.

eg

or

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:

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.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement