May 23, 2002 at 3:29 am
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 evn if a trigger fires?
Thank you,
Marco van Schagen,
Crexx, the Netherlands
May 23, 2002 at 5:46 am
My trriger does not affect me in this way. Are you calling @@ROWCOUNT immediately after the INSERT/UPDATE/DELETE statement? Here is my Example
CREATE TABLE [A] (
[ONE] [int] NOT NULL ,
[TWO] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[ONE]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO A (ONE, TWO) VALUES (1, 'good-bye')
INSERT INTO A (ONE, TWO) VALUES (2, 'B')
INSERT INTO A (ONE, TWO) VALUES (3, 'B')
INSERT INTO A (ONE, TWO) VALUES (4, 'B')
INSERT INTO A (ONE, TWO) VALUES (5, 'B')
GO
CREATE TRIGGER tr_TestIdea ON dbo.A
FOR UPDATE
AS
UPDATE A SET TWO = 'B' WHERE ONE = 3
GO
UPDATE A SET TWO = 'A' WHERE TWO = 'B'
SELECT @@ROWCOUNT
GO
The update will affect 4 rows and the Trigger only one but I get 4 on my @@ROWCOUNT. Are you sure something else is not affecting you?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 23, 2002 at 6:44 am
Thank you.
I tried your example on our database and you are completely right. The select @@rowcount outside the trigger returns the correct value (4) - for the user as if no trigger was executed.
I also tested the value of @@rowcount INSIDE the trigger, and added more code to the trigger in your example. The @@rowcount inside the trigger gave an other value (1) - it did not bother the @@rowcount outside the trigger.
I wrote a similar test on my trigger - same result (not noting any problems indeed).
I really wonder what really is going wrong; appearently my problem is caused somewhere else, I would think it beeing something between COM and SQL server. The COM component uses ADO to execute the commands that are issued.
Next is an excerpt from the code I use to issue the update command.
Where do I loose the rowcount?
Dim objCmd As ADODB.Command
Dim AdoConnection As ADODB.Connection
Dim strSQL As String
Dim lngRowsAffected As Long
Connect AdoConnection 'using our database definition
Set objCmd = New ADODB.Command
strSQL = "INSERT into tblFlexVacdataValues (VAC_GUID,FCF_Code,FVV_RepNum,FVV_Data) VALUES (?,?,?,'" & Replace(strData, "'", "'+CHAR(39)+'") & "')"
Ado_AddInputParameter objCmd, "tblFlexClientdataValues.VAC_GUID", adGUID, 16, strVAC_GUID
Ado_AddInputParameter objCmd, "tblFlexClientdataValues.FCF_Code", adVarChar, 25, strFCF_Code
Ado_AddInputParameter objCmd, "tblFlexClientdataValues.FVV_RepNum", adInteger, , intRepNum
'perform the action
objCmd.CommandText = strSQL
objCmd.CommandType = adCmdText
objCmd.Execute lngRowsAffected
If lngRowsAffected <> 1 Then
Err.Raise ERR_UPDATE_FAILURE, ERR_SOURCE, "Save failed: wrong number of rows [" & lngRowsAffected & "], " & strActionDisplay & " [" & FCS_Target & " " & strFCS_Code & "/" & strFCF_Code & "]"
End If
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply