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

Answer 2

Like I mentioned in the comments, using a TRIGGER for this doesn't make sense when there's a specific object type for this: UNIQUE CONSTRAINT.

--Sample Table
CREATE TABLE dbo.MovieCast (CastID int IDENTITY(1,1),
                            ActorID int NOT NULL,
                            MovieID int NOT NULL,
                            RoleName nvarchar(50));
GO
--Add Constraint
ALTER TABLE dbo.MovieCast ADD CONSTRAINT UQ_MovieActor_MovieCast UNIQUE (ActorID,MovieID);
GO
--Sample Attempts
INSERT INTO dbo.MovieCast (ActorID,
                           MovieID,
                           RoleName)
VALUES(124, 921, 'raj'); --Success
GO

INSERT INTO dbo.MovieCast (ActorID,
                           MovieID,
                           RoleName)
VALUES(124, 928, 'rob'); --Success
GO

INSERT INTO dbo.MovieCast (ActorID,
                           MovieID,
                           RoleName)
VALUES(124, 921, 'Jon'); --Fails
GO
--Clean up
DROP TABLE dbo.MovieCast;

Answer 3

In response to the OP's comment:

the reason for using a trigger and stored procedure was because this was specifically asked to be done.

A UNIQUE CONSTRAINT/UNIQUE INDEX is the right solution here, and it will very likely be much faster. That being said, you can do this with a TRIGGER and THROW the error to the calling SQL.

--Sample Table
CREATE TABLE dbo.MovieCast (CastID int IDENTITY(1,1),
                            ActorID int NOT NULL,
                            MovieID int NOT NULL,
                            RoleName nvarchar(50));
GO
CREATE TRIGGER dbo.trg_UQ_MovieActor_MovieCast ON dbo.MovieCast
AFTER INSERT, UPDATE AS
BEGIN

    SET NOCOUNT ON;

    IF EXISTS (SELECT 1
               FROM dbo.MovieCast MC
               GROUP BY MC.ActorID, MC.MovieID
               HAVING COUNT(*) > 1)
       THROW 55555, --Use a bespoke error number for your environment
             N'A duplicate row has been detected in the trigger ''trg_UQ_MovieActor_MovieCast''. Cannot insert/update duplicate row in object ''dbo.MovieCast''.',
             16; 
END;
GO
--Sample Attempts
INSERT INTO dbo.MovieCast (ActorID,
                           MovieID,
                           RoleName)
VALUES(124, 921, 'raj'); --Success
GO

INSERT INTO dbo.MovieCast (ActorID,
                           MovieID,
                           RoleName)
VALUES(124, 928, 'rob'); --Success
GO

INSERT INTO dbo.MovieCast (ActorID,
                           MovieID,
                           RoleName)
VALUES(124, 921, 'Jon'); --Fails
GO
SELECT *
FROM dbo.MovieCast;
GO
--Clean up
DROP TABLE dbo.MovieCast;

Note that unlike a UNIQUE CONSTRAINT, you aren't informed of the row that causes the error, which means for statements where you affect multiple rows, this will make debugging a little harder.

Separate answer, as the requirement for needing a TRIGGER was never voiced in the question, and my original answer answers the question that was originally asked.

Also, although similar to SQLPro's answer, I disagree with their use of RAISERROR and ROLLBACK, and so I am showing how THROW should be used.