#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    1
    Rep Power
    0

    Using instead of trigger


    Hi,

    I'm trying to use a trigger to make sure no duplicates records would be added to my table (using sql server 2000). I've tried using the "instead of" trigger but for some reason it doen't work.

    The code is :


    Code:
    CREATE TRIGGER tr_verifyDuplicates
    ON tblAL
    INSTEAD OF INSERT
    AS
    BEGIN  
      INSERT tblAL (column1,column2,column3)  
      SELECT column1,column2,column3   
      FROM INSERTED  
      WHERE NOT EXISTS  (
        SELECT * FROM tblAL     
        WHERE column1=(SELECT column1 FROM INSERTED)
        AND column2=(SELECT column2 FROM INSERTED)     
        AND column3=(SELECT column3 FROM INSERTED)
      )
    END
    I thought that the problem may be another column I have in the table which is an identity integer column (the table primary key-lets call it column0) but I don't know how to make it work.
    The table has more than those 4 columns but all the other columns allow null values. Only the identity column and column2 which is a foreign key don't allow null.

    Thanks for your help,
    dmr
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,370
    Rep Power
    391
    define a unique or primary key constraint instead.

IMN logo majestic logo threadwatch logo seochat tools logo