In previous post
here I described a seemingly strange side affect of an INSTEAD OF trigger. Within the INSTEAD OF trigger I didn't update any records - I just returned an error. No rows are actually being affected. However, SSMS (SQL Server Management Studio) shows records *are* being affected. The count is equal to the count that would have been affected had the INSTEAD OF trigger not existed or if it had been disabled.
I wanted to see if this was just SSMS behavior. I created a simple C# Console Application project in Visual Studio 2008 against a SQL Server 2008 Dev Ed database.
Following is database setup code.
CREATE DATABASE MyDB;
GO
USE MyDB
GO
CREATE TABLE dbo.MyTable
(
ID int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
MyColumn varchar(50) NOT NULL
);
GO
-- sample data
INSERT INTO dbo.MyTable (MyColumn) VALUES ('One');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('Two');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('Three');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('Four');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('Five');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('Six');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('Seven');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('Eight');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('Nine');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('Ten');
GO
CREATE TRIGGER dbo.tr_MyTable_StopDataMods
ON dbo.MyTable
INSTEAD OF INSERT, DELETE, UPDATE
AS
BEGIN
SET NOCOUNT ON;
RAISERROR(
N'The data in table %s is static. All data modification operations are blocked. Disable the trigger %s to modify table data.', -- Message text.
16, -- Severity
1, -- State,
'dbo.MyTable', -- first argument (table name)
'dbo.tr_MyTable_StopDataMods' -- second argument (trigger name)
);
END
GO
Test with the following code.
UPDATE MyTable SET MyColumn = 'X';
Should see expected error message and the "10 row(s) affected" message. If you do a SELECT * FROM MyTable; you should see that the data was not modified.
Now let's switch gears from T-SQL over to C# and .NET. Following is complete code for the application. You may have to modify the Connection String as required. The database UPDATE operation is the same as above that you executed in SSMS that resulted in a error, no rows being updated, but SSMS showing "10 row(s) affected."
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
string strConnection = "Data Source=localhost;Initial Catalog=MyDB;Integrated Security=True";
using (SqlConnection cn = new SqlConnection(strConnection))
{
cn.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE MyTable SET MyColumn = 'X';";
int RowsAffected = cmd.ExecuteNonQuery();
Console.WriteLine("Rows Affected: " + RowsAffected.ToString());
}
}
string x = Console.ReadLine(); // Keep Console from closing
}
}
}
Run the application in debug mode. Should error out on the cmd.ExecuteNonQuery command with an unhandled SqlException with the expected error message: "The data in table dbo.MyTable is static. All data modification operations are blocked. Disable the trigger dbo.tr_MyTable_StopDataMods to modify table data." See the screenshot below. In the locals window take a look at cmd.Non-Public members._rowsAffected. Shows a value of 10 (ten). Now isn't that interesting. This side affect associated with the INSTEAD OF trigger that updates zero rows is also seen when making calls through other APIs. To confirm this behavior you could possibly do additional testing using other INSERT, UPDATE, and DELETE commands.
Any feedback or ideas that you have will be appreciated. Thanks!
Update:
I modified the INSTEAD OF trigger and removed the RAISERROR command. Now the INSTEAD OF trigger just has the SET NOCOUNT ON command.
ALTER TRIGGER dbo.tr_MyTable_StopDataMods
ON dbo.MyTable
INSTEAD OF INSERT, DELETE, UPDATE
AS
BEGIN
SET NOCOUNT ON;
END
GO
When you rerun the C# Console app, it runs successfully without error and shows the following.
If you again look at the underlying data in the table - it is unaffected by the UPDATE statement. No rows were actually updated.
To further confirm disable the trigger and rerun the C# Console app. It should again show Rows Affected: 10. This time when you query the data in the table - the data will have been updated.
According to the MSDN documentation located
here, the System.Data.SqlClient.SqlCommand.ExecuteNonQuery() method "Executes a Transact-SQL statement against the connection and returns the number of rows affected." The documentation also states "For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1." The documentation is not very helpful.
It is clear that when INSERT, UPDATE, or DELETE statements are executed against a table or view with an INSTEAD OF trigger, regardless of what code is actually executed within the INSTEAD OF trigger, the following is occuring: A SELECT statement is being run against the table or view to determine the number of rows that would have been affected had the trigger not existed. This value is being passed back to the process/application requesting the INSERT, UPDATE, or DELETE operation as the count of rows being affected even though the actual number of rows affected could be zero, the same, or something completely different.
Again, thanks for reading and any feedback is appreciated. Thanks!