Trigger throws primary key violation error: Cannot insert duplicate key in object

Question

create table Hotel
(
    hotel_id integer primary key NOT NULL,    
    hotel_name varchar(50) NOT NULL UNIQUE,
    location_ varchar(50) NOT NULL,
    rates varchar(10) check(rates in ('5star','4star','3star','2star','1star')),    
);    

create table Room
(
    room_no integer primary key NOT NULL,
    total_rooms integer NOT NULL,
    room_price real check (room_price >= 0),
    hotel_id integer foreign key references Hotel
);    

insert into Hotel values(1,'sevensay','gamapaha','4star')
insert into Hotel values(2,'sarasvi','gamapaha','3star')
insert into Hotel values(3,'galadari','colombo','5star')
insert into Hotel values(4,'kingsbary','colombo','4star')
insert into Hotel values(5,'niramliii','gamapaha','5star')
insert into Hotel values(6,'sadalnka','kandy','3star')
insert into Hotel values(7,'sri lnkani','kandy','5star')

insert into Room values(100,10000,1)
insert into Room values(220,20000,2)
insert into Room values(160,1000,3)
insert into Room values(100,12000,4)
insert into Room values(50,15000,5)
insert into Room values(80,10000,6)
insert into Room values(100,20000,7)

drop table Room
drop table Hotel

select * from Hotel
select * from Room

create trigger rooms_availability
on Room
for insert
as
begin    
    declare @hotel_id integer
    declare @total_rooms integer     

    select @hotel_id = hotel_id from  inserted
    select @total_rooms = count(*) from Room where hotel_id = @hotel_id
    rollback transaction

    if @total_rooms > 80    
    begin    
        print 'we have only 80 rooms .we cannot book the other rooms'   
    end
end

insert into Room values(300,10000,6)

How can I handle this error?

Msg 2627, Level 14, State 1, Line 25
Violation of PRIMARY KEY constraint 'PK__Room__1967F4191F8BEC00'. Cannot insert duplicate key in object 'dbo.Room'. The duplicate key value is (506).
The statement has been terminated.

Although the error is fairly clear "Cannot insert duplicate key in object 'dbo.Room'" - the solution is not to insert a duplicate. You should add code explaining what the trigger is trying to do rather than make use wade through it and try and make sense of it. — Dale K
i need to use trigger room table to check the availability rooms . — Buddhika Senanayaka
You need to read about Using Inserted And Deleted Tables because they can contain more than one row which you aren't handling. — Dale K

Dale K Dale K · Accepted Answer · 2020-04-28T21:46:31

The error you are showing is caused from trying to insert a row into Room with a duplicate primary key. However the code you provide doesn't have that error. And if you use an identity column (recommended for primary keys) you will never have that issue.

The more important issue is in your trigger, where you are not handling that fact that Inserted can have multiple rows. You can handle this correctly using a set based approach:

create trigger rooms_availability
on Room
for insert
as
begin    
    if exists (
      select 1
      from Room
      where hotel_id in (select hotel_id from Inserted)
      group by hotel_id
      having count(*) > 80
    )
    begin
        print 'We have only 80 rooms. We cannot book the other rooms.'   
        rollback;
    end;
end

Note: I assume Print is being used for debugging. Once its working you will want to use throw.

Trigger throws primary key violation error: Cannot insert duplicate key in object

1 Answers