Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    364

    Smile Unanswered: Trigger Critique

    Here is a trigger I wrote to check for duplicate taxid's in my producer table. Duplicate taxid's with an '' value are allowed. I'm green when it comes to trigger coding so would anyone care to review my code and reply if there's a more efficient way to implement the trigger or handle errors.

    Thanks PLJ

    create trigger dbo.tib_producer on dbo.producer for insert as
    begin
    declare
    @numrows int,
    @errno int,
    @errmsg varchar(255)
    select @numrows = @@rowcount
    if @numrows = 0
    return
    /* Check for duplicate tax_id if inserted is not null */
    if update(tax_id)
    begin
    if exists(select tax_id
    from producer
    where tax_id in (select tax_id
    from inserted)
    and tax_id <> ''
    group by tax_id having count(*)>1)
    begin
    select @errno = 50002,
    @errmsg = 'Tax ID already Exists'
    goto error
    end

    end
    return
    /* Errors handling */
    error:
    raiserror @errno @errmsg
    rollback transaction
    end

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The first issue I see with this trigger is that the logic seems to assume that only one record is being inserted at a time. I think if more than one record is inserted, your entire operation could fail if a single duplicate exists. Is this what you want?

    Regarding your code:

    Use an inner join instead of an IN() clause:

    /* Check for duplicate tax_id if inserted is not null */
    if update(tax_id)
    begin
    if exists
    (select tax_id
    from producer
    inner join inserted on producer.tax_id = inserted.tax_id
    where tax_id <> ''
    group by tax_id having count(*)>1)
    begin
    select @errno = 50002,
    @errmsg = 'Tax ID already Exists'
    goto error
    end


    Also, why are you referencing @@RowCount? If you want the number of rows inserted, then select count(*) from inserted. I'm not sure you can rely on @@RowCount to give you what you want. I guess I don't see the point of the logic either, because if no rows are affected, then the trigger won't run anyway.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2003
    Posts
    364
    Thanks for the reply blindman. I'm not sure what the @@rowcount does either. However, it's been in a lot of the books I've read. None of them explain why it's done. Do you think it may have something to do with nested triggers?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I wouldn't do this...

    First sanitize your data...clean it up, in other words...then add the appropriate constraints

    @@ROWCOUNT tells you the number of rows affected by a DML operation...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •