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 |
|
mheindl
Starting Member
2 Posts |
 Posted - 2006-10-09 : 15:14:20
|
| We've run into an odd situation with an INSTEAD OF INSERT trigger on a SQL 2005 database.The trigger is being used to hash sensitive information in two columns on a table. One of these columns has a unique constraint on it. Before the INSERT into the main table is actually performed, we perform an INSERT into a reference table so that we can get back to the original data.This all works well when our application (a web app) is performing a single insert at a time via a stored procedure. However, when we tested another stored procedure which performs multiple inserts into the main table (using a INSERT INTO/SELECT construct) we get odd results. On the INSERT attempt for the second row into our INSERT INTO/SELECT construct the unique key constraint is violated. We've confirmed that the data being SELECTed for INSERT has unique values for this column. In fact, when we've disabled the INSTEAD OF INSERT trigger and allowed the original INSERT to occur we do not encounter the unique constraint violation.I guess we're a little fuzzy on what the 'inserted' table contains when the INSTEAD OF INSERT trigger is executed. Is there only one row in the 'inserted' table? Or are there multiple rows due to the multiple rows that will be inserted from the INSERT INTO/SELECT?Does anyone know of any limitations with using an INSTEAD OF INSERT trigger within an INSERT INTO/SELECT? |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2006-10-09 : 15:24:12
|
| The inserted table will contain multiple rows if multiple rows are returned by the SELECT clause of the INSERT INTO/SELECT construct. I'm not entirely clear on whether your proc is doing multiple inserts, inserting multiple rows, or both. Post the code of your proc and the trigger, DDL to create the tables, and some sample data and expected result. This should ensure you get some useful feedback.Mark |
 |
|
|
mheindl
Starting Member
2 Posts |
Posted - 2006-10-09 : 15:35:34
|
| I think the fact that the 'inserted' table can have multiple rows is the cause of our problem. We perform a SELECT @var FROM inserted at the top of the trigger and use this value later on in the INSERT to the main table. That is most likely the source of the unique constraint violation. When the trigger was developed, we were under the impression that the 'inserted' table held a single row at a time, even in situations where multiple rows were being inserted.Thanks for the quick response, BTW. |
|
|
|
|
|
|
Problem with INSTEAD OF INSERT trigger