ALTER proc [dbo].[sp_AddMovies]
--input parameters 
(
@Title varchar(100),
@GenreID int,
@RatingID int,
@Length varchar(5),
@ReleaseYear varchar(5),
@TotalInStock int = 0,
@TotalRented int = 0
)

as
begin


--do not to return the count of rows affected by each sql operation
--disabling the row count improves performance
set nocount on


--validate genre id and rating id;
--checking in SP rather than trigger before insert happens;
--checking for invalid genreid/ratingid in trigger will be fired only after insert where it
--doesn't even happen because of the FK constraint between tables, Movies, Genre, and Rating
if not exists (select 1 from Genre where GenreID = @GenreID)
	begin
		--returns number of active transactions;
		--the check is to see if there's a valid open transaction in which case roll it back and then raise the respective error
		if @@trancount > 0
			rollback transaction
			raiserror ('Genre %d Invalid',10,1,@GenreID)
	end

if not exists (select 1 from Rating where RatingID = @RatingID)
	begin
		--returns number of active transactions;
		--the check is to see if there's a valid open transaction in which case roll it back and then raise the respective error
		if @@trancount > 0
			rollback transaction
			raiserror ('Rating %d Invalid',10,1,@RatingID)
	end


begin transaction

	--insert record into database
	insert into Movies
	values (upper(@Title),@GenreID,@RatingID,@Length,@ReleaseYear,@TotalInStock,@TotalRented)

	-- transaction error handling; if error number is not zero then there's a failure, rollback transaction, and print message
	if @@error <> 0
		begin
			rollback transaction
			select 'Insert Failed'
			return
		end

	else
		begin
			select 'Movie Added Successfully'
		end

commit transaction

end

ALTER trigger [dbo].[trg_i_movies]
on [dbo].[Movies]
for insert
as
begin


--local variable
declare @Title varchar(100)

--assign value to local variable
select @Title = (select Title from inserted)

--validate duplicate movie
if exists (select 1 from Movies where Title = @Title)
	begin
		--returns number of active transactions;
		--the check is to see if there's a valid open transaction in which case roll it back and then raise the respective error
			rollback transaction
			raiserror ('Movie %s already exists',10,1,@Title)
	end


end

CREATE PROCEDURE dbo.usp_AddMovies
(
	@Title varchar(100),
	@GenreID int,
	@RatingID int,
	@Length varchar(5),
	@ReleaseYear varchar(5),
	@TotalInStock int = 0,
	@TotalRented int = 0
)
AS

SET NOCOUNT ON

INSERT		Movies
		(
			Title,
			GenreID,
			RatingID,
			Length,
			ReleaseYear,
			TotalInStock,
			TotalRented
		)
SELECT		UPPER(@Title),
		@GenreID,
		@RatingID,
		@Length,
		@ReleaseYear,
		@TotalInStock,
		@TotalRented
FROM		Genre AS g
INNER JOIN	Rating AS r ON r.RatingID = @RatingID
WHERE		g.GenreID = @GenreID
		AND NOT EXISTS (SELECT * FROM Movies WHERE Title = @Title)

IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Movie %s is not inserted.', 16, 1, @Title)
		RETURN -100
	END

quote:

---

Originally posted by Peso

No need for trigger.

CREATE PROCEDURE dbo.usp_AddMovies
(
	@Title varchar(100),
	@GenreID int,
	@RatingID int,
	@Length varchar(5),
	@ReleaseYear varchar(5),
	@TotalInStock int = 0,
	@TotalRented int = 0
)
AS

SET NOCOUNT ON

INSERT		Movies
		(
			Title,
			GenreID,
			RatingID,
			Length,
			ReleaseYear,
			TotalInStock,
			TotalRented
		)
SELECT		UPPER(@Title),
		@GenreID,
		@RatingID,
		@Length,
		@ReleaseYear,
		@TotalInStock,
		@TotalRented
FROM		Genre AS g
INNER JOIN	Rating AS r ON r.RatingID = @RatingID
WHERE		g.GenreID = @GenreID
		AND NOT EXISTS (SELECT * FROM Movies WHERE Title = @Title)

IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Movie %s is not inserted.', 16, 1, @Title)
		RETURN -100
	END

---

E 12°55'05.63"
N 56°04'39.26"

---