Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-05-22 : 08:05:11
|
| David writes "For reasons that I really don't want to get into, I have a table that is supposed to store unique values, is letting duplicate values in, and can't have a primary key applied or unique constraint on the required columns (oh the pain!)So I've decided to place an insert trigger on the table to stop further duplicate rows being inserted. Unfortunately, it ain't working as it's supposed to. The trigger is :CREATE TRIGGER BlockDuplicatesFOR INSERTASIF EXISTS (SELECT * FROM inserted AS i INNER JOIN target AS t ON (i.col1 = t.col1 AND i.col2 = t.col2))BEGIN ROLLBACK TRANSACTION RAISERROR('Attempting to insert duplicates', 16, 1)ENDWhen I try to insert a brand-new record, the new record gets inserted into the table, and the trigger fires generating the error message. By my understanding triggers, the error should only be raised when a matching record already exists in the target table before the insert operation is begun. What's wrong with my code?!" |
|
|
DanG
Starting Member
10 Posts |
Posted - 2003-05-22 : 19:15:28
|
| As your trigger fires AFTER the insertion took place andin an insertion new records are inserted into the 'target' table and the inserted table you get duplicates.Use an instead of trigger that fires before an insertion take place. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-22 : 19:27:43
|
| Or you can use the after triggerif exists (select count(*) from inserted AS i INNER JOIN target AS t ON i.col1 = t.col1 AND i.col2 = t.col2 group by i.col1, i.col2 having count(*) > 1)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Using triggers to enforce entity integrity