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 - 2002-10-16 : 09:12:14
|
| Brandon writes "It appears that I have an insert trigger that is not functioning correctly on rows that are inserted by a multiphase data pump process. I suspect it is one of two things, but cannot find documentation to clarify which one. First possibility: data added through a multiphase data pump is actually a "bulk" insert and thus does not cause an Insert trigger to fire by default.If this is the case, how do I get the trigger to fire?Second possibility: I have found evidence that an insert trigger must be written in such a way to update all rows at once... In other words, when a group of rows are inserted at once, the trigger does not fire once for each row, but actually executes the code one time on multiple rows... Thus, the trigger must be written in a way to update using proper syntax for multiple rows.If this is the case, I would love to see an example of how I would update Col1 (default is NULL) to combine the first three characters in Col2, the first three characters in Col3 and all of col4 (an identity column). So, I add a row that has COL2=BRANDON, COL3=UTAH, COL4=3534 and I want my insert trigger to update COL1=BRAUTA3534... and I want it to still work with multiple inserts.I am using SQL Server 2000. Thanks!" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-16 : 11:04:39
|
| Easy to test - if the trigger fires then it is ok, if it doesn't then it's not.If you add an activex transformation then it should insert row by row (with the associated degradation in performance).trigger to cope with batch insertsupdate tblset Col1 = left(Col2,3) + left(Col3,3) + convert(varchar(20),col4)from insertedwhere tbl.pk = inserted.pk==========================================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. |
 |
|
|
|
|
|
|
|
Multiphase data pump and Insert triggers