Check for duplicate InvoiceIDs

gistfile1.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE CIS
GO

-- =============================================
-- Author:  	Jeremy Fowler
-- Create date: 3/25/2013
-- Description:	Check for duplicate InvoiceIDs
-- =============================================
CREATE TRIGGER DupCheck
   ON  [dbo].[taaLogInvoiceExport]
   AFTER INSERT
AS 
BEGIN
    DECLARE @CurUser varchar(50), @msg varchar(200)
    SET NOCOUNT ON;

    -- Check for non-failed duplicate InvoiceIDs
    SELECT @CurUser=e.[UserID] FROM taaLogInvoiceExport e
      JOIN INSERTED i ON e.[InvoiceID] = i.[InvoiceID] 
       AND e.[Description] = i.[Description]
    WHERE e.[Description] = 'Start Invoice Export'
      AND e.[RecordKey_PK] <> i.[RecordKey_PK]
      AND ISNULL((SELECT 'FAILED' FROM taaLogInvoiceExport f 
           WHERE f.[InvoiceID] = e.[InvoiceID]
             AND f.[BatchNum] = e.[BatchNum]
             AND f.[Description] like '%FAILED%'),'OK') <> 'FAILED'
	
    IF @CurUser IS NOT NULL 
	BEGIN
	  SET @msg = 'Invoice export has already been started by '+@CurUser
	  RAISERROR (@msg, 16, 1)
	  ROLLBACK TRAN
	  RETURN
	END
END
GO


/****** Object:  Index [BatchNum]    Script Date: 03/27/2013 12:33:11 ******/
CREATE NONCLUSTERED INDEX [BatchNum] ON [dbo].[taaLogInvoiceExport] 
(
	[BatchNum] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, 
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/****** Object:  Index [InvoiceDesc]    Script Date: 03/25/2013 15:52:25 ******/
CREATE NONCLUSTERED INDEX [InvoiceDesc] ON [dbo].[taaLogInvoiceExport] 
(
	[InvoiceID] ASC,
	[Description] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, 
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO