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-05-28 : 08:35:45
|
| Marco writes "Hi,We have an asp web aplication using COM on a SQL Server 2000 database. The application uses COM to perform the database access. On altering data, the code checks the @@rowcount to see if the intented insert or update was succesfull - this is done on changes in various colums in various locations in the code. Now we need to add a trigger to update changed data into other tables. This is because we start migrating to a different data structure - while keeping most of the original code intact.The problem is now, that the trigger beeing fired erases the @@rowcount. I can imagine others to have experienced the same problem. So, the big question is here:Is there a solition/workaround to keep the @@rowcount on update/insert/deletes on a table even if a trigger fires?Thank you,Marco van Schagen,Crexx, the Netherlands" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-28 : 09:09:07
|
| The trigger is not 'erasing' your rowcount global variable, rather it is setting it to a new value because the trigger is a new statement. @@rowcount is set to 0 by any statement that does not return rows.That doesn't answer your question though . . . and I don't believe their is a good one (answer). I believe you will need to modify your error handling code to validate success based on some measure other than @@rowcount . . .<O> |
 |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-05-28 : 10:00:35
|
| One possible solution thoutg would require lot of coding. is checking for errors on every dml statement in Trigger. if it generates a error returning a -1 value otherwise the last statement in the trigger should return 0. if it returns 0 , it means all everythign in the trigger was executed succesfully.-------------------------------------------------------------- |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-05-28 : 10:28:12
|
| Any chance a seperate 'rowcount' table can be used to moniter the @@rowcount? Create this table (it will contain 1 column, maybe 2) and modify your trigger to include update rowcountTableset rowcount = @@rowcountThis will HAVE to be the first statement in the trigger. Since triggers have access to the inserted table, instead of using @@rowcount you maybe better off just using rowid from inserted (or whatever your auto int column is called)This table will contain only one record, and it will be the last @@rowcount before the trigger fired. Then modify your other statements to instead of using @@rowcount, use select rowcount from rowcounttableHaven't tested it, but it might be worth looking into |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-28 : 15:43:52
|
Building on Page's answer - you've square-pegged a round hole by using @@rowcount instead of @@error for your error handling. At this point your best bet is to globally search and replace "if @@rowcount = 0" with "if @@error != 0".quote:
The trigger is not 'erasing' your rowcount global variable, rather it is setting it to a new value because the trigger is a new statement. @@rowcount is set to 0 by any statement that does not return rows.That doesn't answer your question though . . . and I don't believe their is a good one (answer). I believe you will need to modify your error handling code to validate success based on some measure other than @@rowcount . . .<O>
setBasedIsTheTruepath<O> |
|
|
|
|
|
|
|
|
@@rowcount lost when trigger fires