Validation trigger not working in SQL Server-CodePudding

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

CodePudding user response：

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

create table salesman(id int primary key, type varchar(20) check (type in ('a','b')))

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
            ROLLBACK TRANSACTION
            PRINT 'employee type not valid'
        END
    END

go

insert into salesman(id,type) values (3,'c') -- fails

insert into salesman(id,type) values (3,'a') -- succeeds

CodePudding user response：

If you want to check the value of a column there are more efficent ways. Best practice is to define constraints. When the value are stored in a table then use FOREIGN KEY constraint; in other case use CHECK constraint.