Raise trigger for duplicate prevention-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

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 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(55555, '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);