CODEWITHSUNDEEP
sqlsql-servertriggers
Mohit Kumar
Mohit Kumar

Reputation: 1975

Triggers in SQL Server

There are two Table Table_Trigger and Table_Trigger1.I have created a trigger on Table_Trigger1. It works perfectly.Below is my Trigger script -

CREATE TRIGGER Test_Trigger
ON Table_Trigger1

INSTEAD OF INSERT

AS
SET NOCOUNT ON

INSERT INTO Table_Trigger1(FirstName)
SELECT FirstName from INSERTED I
WHERE I.firstName in (SELECT FirstName from Table_Trigger)

when my where condition is true, I get a message (1 row(s) affected) and value save in Table.But when my where condition is false, that time I also get a message (1 row(s) affected) and value not save in Table.

My question is - If where condition is false and value don't save in table so why I got a (1 row(s) affected) message. what is meaning of this message.

Thanks in advance.

Upvotes: 1

Views: 229

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

In a nutshell, the "rows affected" for your original insert is ignorant of what's going on behind the scenes in your trigger. It passes one row through and considers that row "affected."

I tried to find some documentation to back this up. The closest I could come was the documentation on SqlCommand.ExecuteNonQuery, which 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.

Of course, the SET NOCOUNT ON in your trigger is suppressing the return of the number of rows affected within the trigger, so you're only seeing the result of the original insert statement.

Upvotes: 3

Related Questions