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

Hi,

Use this script:-

Create TRIGGER tgPreventJobDupes ON [dbo].JOB FOR INSERT AS
DECLARE @InvoiceNo VarChar(9)
IF (SELECT count(*) FROM JOB , inserted WHERE job.InvoiceNo =
inserted.invoiceno)>1
begin
RollBack Transaction
end
else
commit tran

--
Thanks
Hari
MCDBA
[quoted text, click to view]
records to a table (dbo.JOB) if the field value of Inserted.InvoiceNo
already exists in dbo.JOB.InvoiceNo.
[quoted text, click to view]
records into the table. I've been working on this most of the day, and am a
bit baffled.
[quoted text, click to view]