MS SQL Check for duplicate in two fields

Question

I am trying create a trigger that will check if the Author already exist in a table based on a combination of their first and last name. From what Ive been reading this trigger should work, but when I try to insert any new author into the table it gives the "Author exists in table already!" error even though I am inserting an author that does not exist in the table.

Here is the trigger

USE [WebsiteDB]
GO
CREATE TRIGGER [dbo].[tr_AuthorExists] ON [dbo].[Authors]
AFTER INSERT
AS

if exists ( select * from Authors
    inner join inserted i on i.author_fname=Authors.author_fname AND i.author_lname=Authors.author_lname)
begin
    rollback
    RAISERROR ('Author exists in table already!', 16, 1);
End

Here is the table

CREATE TABLE [dbo].[Authors](
    [author_id] [int] IDENTITY(1,1) NOT NULL,
    [author_fname] [nvarchar](50) NOT NULL,
    [author_lname] [nvarchar](50) NOT NULL,
    [author_middle] [nvarchar](50) NULL,
CONSTRAINT [PK_Authors] PRIMARY KEY CLUSTERED 
(
    [author_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Any assistance would be appreciated!

Answer 1

You will need to do this as an INSTEAD of trigger. This also means you need to actually perform the insert inside the trigger. Something along these lines.

CREATE TRIGGER [dbo].[tr_AuthorExists] ON [dbo].[Authors]
instead of insert
AS
    set nocount on;

    if exists 
    ( 
        select * from Authors a
        inner join inserted i on i.author_fname = a.author_fname AND i.author_lname = a.author_lname
    )
    begin
        rollback
        RAISERROR ('Author exists in table already!', 16, 1);
    End
    else
        insert Authors
        select i.author_fname
            , i.author_lname
            , i.author_middle
        from inserted i