November 29th, 2003, 05:36 AM
Join Date: Nov 2003
Time spent in forums: < 1 sec
Reputation Power: 0
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 :
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)
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,