December 4, 2003 at 3:05 pm
SQL Server 2000 SP3:
There's a view in one of our databases spanning three tables. To enable it to accepts inserts, I wrote a INSTEAD OF INSERT TRIGGER on it to basically write to the first base table from the INSERTED table, take the @@IDENTITY value and then do the next insert into the 2nd dependent table and so on. Based on the restriction that all view columns, whether or not updatable, need to be in the INSERT statement, I created the statement and it worked fine for a single insert.
I tried then doing a INSERT INTO ....SELECT FROM another table, i.e a bulk insert and it failed citing that a particular UNIQUE constraint on one column in one of the tables
was violated.
I created a sproc with a cursor to do the inserts row by row and that worked fine. So my question is, is this a behavior by design? or is this something that's specific to details of the constituent tables....?
December 5, 2003 at 6:34 am
Bulk inserts don't fire triggers by default. You shoud specify the fire FIRE_TRIGGERS syntax in the bulk insert command.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply