November 29th, 2003, 04:36 AM
Using instead of trigger
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 :
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.
CREATE TRIGGER tr_verifyDuplicates
INSTEAD OF INSERT
INSERT tblAL (column1,column2,column3)
WHERE NOT EXISTS (
SELECT * FROM tblAL
WHERE column1=(SELECT column1 FROM INSERTED)
AND column2=(SELECT column2 FROM INSERTED)
AND column3=(SELECT column3 FROM INSERTED)
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,
November 29th, 2003, 12:56 PM
define a unique or primary key constraint instead.