CODEWITHSUNDEEP
sqlsql-servert-sql
Ben Deacon
Ben Deacon

Reputation: 1

SQL Server trigger that stops rows from being added to a table if one of the columns has a value that is not already in the table

I have a table in SQL Server that has the following four columns:

ID_Number
Firstname
Lastname
Group

I am in need of a trigger that does not allow new rows to be added to this table, if the row that is being added contains a value that is not already present in the Group column of the table. Ideally, the trigger would also create an Error message (potentially using the RAISERROR command) that would highlight the fact that the row being added contains a value for the Group column that is not already present in the database.

I am new to T-SQL and any help with this problem will be greatly appreciated.

Upvotes: 0

Views: 62

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You are doing this wrong. You want a separate table for groups (and group is a bad name because it is a SQL reserve word). I would recommend:

create table groups (
    group_id int identity(1, 1) primary key,
    group_name varchar(255) not null unique
);

create table <whatever> (
    id_number int identity(1, 1) primary key,
    first_name varchar(255),
    last_name varchar(255),
    group_id int not null,  -- has to be defined
    constraint fk_group_id foreign key (group_id) references groups(group_id)
);

Voila! Every row has to have a group. And the group has to already be defined. You need to insert valid group names before inserting into your table. So:

insert into groups (group_name)
    values ('group anme')

insert into t (first_name, last_name, group_id)
    select @first_name, @last_name, group_id
    from groups g
    where g.group_name = @group_name;

EDIT:

You can also use group_name as the primary key:

create table groups (
    group_name varchar(255) not null primary key
);

create table <whatever> (
    id_number int identity(1, 1) primary key,
    first_name varchar(255),
    last_name varchar(255),
    group_name varchar(255) not null,  -- has to be defined
    constraint fk_group_id foreign key (group_id) references groups(group_id)
);

You still need to insert the group_name first into groups -- signifying that it is a valid value. However, I am not a fan of using strings as primary keys because they are generally less efficient (primarily due to the extra space the take up with foreign key references).

Upvotes: 3

Related Questions