Re: Prevent insert duplicate record TRIGGER

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 06/11/04 

Date: Fri, 11 Jun 2004 10:14:41 +0530

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
"Andre Ranieri" <Andre Ranieri@discussions.microsoft.com> wrote in message
news:87187F42-4E07-4AA2-B5DE-00B207D7D6CF@microsoft.com...
>
> 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

Relevant Pages

  • Re: adding record to sql db
    ... > create trigger tD_BCustomerCA on BCustomerCA for DELETE as ... > declare @numrows int, ...
    (microsoft.public.dotnet.languages.vb)
  • Prevent insert duplicate record TRIGGER
    ... I'm hoping someone can help me refine a trigger which will prevent writing records to a table if the field value of Inserted.InvoiceNo already exists in dbo.JOB.InvoiceNo. ... DECLARE @InvoiceNo VarChar ...
    (microsoft.public.sqlserver.mseq)
  • Re: how to assign the contents of a field to a variable
    ... what happens if the MSMQ server is ... teh requirements of the trigger are to export an XML document to MSMQ ... DECLARE @int_msmqqueue INT ... IF @int_result 0 GOTO ErrorHandler ...
    (microsoft.public.sqlserver.programming)
  • DROP TRIGGER in Prozedur?
    ... DECLARE trig_name char; ... IF NOT done THEN ... Rufe ich diese Prozedur mit CALL drop_trigger() auf, erhalte ich die lapidare Fehlermeldung "ERROR 1360: ...
    (de.comp.datenbanken.mysql)
  • RE: Cursor Not Running Correctly.. Need Help/Suggestions...
    ... It is not a good idea to use a trigger to send mail as this could severly ... > DECLARE @NewOrderID INT ... > INNER JOIN Orders ... > DECLARE @SubTotal MONEY ...
    (microsoft.public.sqlserver.programming)