Ignoring an Insert Using a Trigger

I have a database with three tables User(userID, Firstname, LastName), Movies(MovieID, MovieName), and UserMovies(MovieID, userID). I also have a trigger in the User table that I would like to have run After Insert that determines if the supplied userID is already existing. If the supplied userID exists in the table, how do I go about ignoring the insert that triggered the trigger and began manipulating the Movies table (my next INSE开发者_Python百科RT command)? I tried using RETURN

if exists(select insert.userID FROM inserted)

return

but that's not working for me. Thank you.

If you want to restrict data insertion based on some condition, you should do it in the before trigger. Here, check to see if the user_id already exists. If it does, then you can restrict inserting that data in your table by rolling back the transaction.

Raise an exception in your trigger function.

RAISERROR ('Error Text', 11, 1);

I don't know of another way to "ignore" an insert in a trigger function. Since the function was triggered by an INSERT command, the INSERT should either happen or an error should occur.

Another way to do this would be to write a stored procedure that checks for your condition before attempting the INSERT.