Prevent insert duplicate record TRIGGER

From: Andre Ranieri (Ranieri_at_discussions.microsoft.com)
Date: 06/10/04

  • Next message: Hari: "Re: Prevent insert duplicate record TRIGGER" 
    Date: Thu, 10 Jun 2004 15:16:02 -0700

    I'm hoping someone can help me refine a trigger which will prevent writing records to a table (dbo.JOB) if the field value of Inserted.InvoiceNo already exists in dbo.JOB.InvoiceNo.

    The syntax checks out on the trigger but I'm still able to write duplicate records into the table. I've been working on this most of the day, and am a bit baffled.

    Any suggestions would be much appreciated.

    Thanks,

    Andre Ranieri

    CREATE TRIGGER tgPreventJobDupes ON [dbo].JOB FOR INSERT AS
    /*
            InvioceNo is a varchar field in dbo.Job table. INSERT trigger prevents writing duplicate record if InvoiceNo field value already exists.

    */
    DECLARE @InvoiceNo VarChar(9)
    SET @InvoiceNo = (SELECT TOP 1 InvoiceNo FROM INSERTED)
    IF (SELECT COUNT(*) FROM JOB WHERE (InvoiceNo = @InvoiceNo)) != 0
    BEGIN
            RollBack Transaction
    End

  • Next message: Hari: "Re: Prevent insert duplicate record TRIGGER"