Using instead of trigger

dmr999 · November 29th, 2003, 07:19 AM

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

Jeff Mason · November 29th, 2003, 02:35 PM

When you say it "doesn't work", what do you mean?

I don't know, but it might be easier and more straightforward to set up a unique constraint on the table, instead of using a trigger.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com