Raise trigger for duplicate prevention

Question

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';

Answer 1

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 :

CREATE OR ALTER TRIGGER prevent_recast 
ON movie_cast$ 
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON
IF NOT EXISTS (SELECT *
               FROM   movie_cast$ as t
                      JOIN inserted i 
                         ON i.mov_id = t.mov_id
                            AND i.act_id = t.act_id
                            AND i.role = t.role
               HAVING COUNT(*) = 1)
RETURN;
ROLLBACK;
RAISERROR('Duplicate Data : this actor is already cast for this movie.', 16, 1);
GO

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:

ALTER TABLE movie_cast$
   ADD UNIQUE (actor_id, mov_id, role_name);

Please DO NOT modify my code... Just suggests some corections