I am trying to raise a trigger if duplicate is being inserted into the table movie_cast$
. But the error is not being raised even if duplicate is being inserted. This is the stored procedure and trigger. Please help.
create or alter procedure up_cast_actor
@actor_id integer,
@mov_id integer,
@role_Name varchar(122)
as
begin
set nocount on
insert into movie_cast$
values (@actor_id, @mov_id, @role_name);
end;
go
create or alter trigger prevent_recast
on movie_cast$
after update
as
begin
set nocount on
if exists (
select *
from movie_cast$ as t
inner join inserted i on
i.mov_id = t.mov_id
and i.act_id = t.act_id
and i.role = t.role
)
begin
--rollback
raiserror( -20001, -1,-1, 'This actor is already cast for this movie.'); --to restrict the insetion`.
RAISERROR ('Duplicate Data', 16, 1);
end;
end;
go
EXECUTE up_cast_actor 124, 921, 'raj';
EXECUTE up_cast_actor 124, 928, 'rob';
EXECUTE up_cast_actor 124, 921, 'raj';
First : you forget a ROLLBACK statement to cancel the transaction
Second : you forget to count (HAVING)
Third : you do no have the right syntax for RAISERROR
The code must be :
Of course as @Larnu says, this is a stupid thing to do a cancel on a transaction that is made of interpreted code (Transact SQL) and runs after the INSERT, instead of using a UNIQUE constraints that runs in C language and acts before the insert !
The constraint will be as simple as:
Please DO NOT modify my code... Just suggests some corections