November 29, 2003 at 4:14 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 :
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
November 29, 2003 at 7:45 am
Looks like you have a requirement to "skip" offending records without failing the insert, otherwise why not just to create unique constraint on all three columns?
But lets assume that you just want to skip non-unique records. The clauses
quote:
columnX=(SELECT columnX FROM INSERTED)
do not do you any good because select in this case return scalar value which is usually the value from the last row of data. On other words, if last inserted row is kosher, all rows will be inserted. Something like that might be what you want:
CREATE TRIGGER tr_verifyDuplicates
ON tblAL
INSTEAD OF INSERT
AS
BEGIN
INSERT tblAL (column1,column2,column3)
SELECT column1,column2,column3
FROM INSERTED i
WHERE NOT EXISTS (
SELECT * FROM tblAL
WHERE column1=i.column1
AND column2=i.column2
AND column3=i.column3
)
END
Cheers
--
georged
Edited by - georgedo on 11/29/2003 07:45:34 AM
--
George
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply