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

    Question Unanswered: Check for Duplicates Help

    I have a table that has an ssn column that is nullable. I want to allow duplicate null values but if someone tries to insert or update th column with a value that is not null I need to check to see if the value already exists and if so generate an error.

    Can anyone tell or better yet provide an example of the best way to do this. I'm guessing a trigger but I'm pretty green when it comes to writing efficient triggers.

    Thanx in advance!!!

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    You can not use unique index but trigger will be good for your case:

    drop table test
    go
    create table test(ssn varchar(7))
    go
    alter trigger no_duplicates on test
    for insert,update
    as
    if exists(select ssn from test
    where ssn in(select ssn from inserted)
    and ssn is not null
    group by ssn having count(*)>1)begin
    raiserror('duplicates!!!',0,1)
    rollback tran
    end

  3. #3
    Join Date
    Sep 2003
    Posts
    364

    Talking

    Thanks a bunch snail!!!

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No kidding?

    DB2 has something SQL Server doesn't?

    UNIQUE WHERE NOT NULL

    Nothing like that here?

    I'm shocked!
    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.

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    that's an extension for fat ibm dba's

Posting Permissions

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