SQL Server - Does Trigger Affects @@Rowcount

SQL Server - does trigger affects @@Rowcount?

@@ROWCOUNT is tied to the scope of your current execution and is therefore unaffected by a trigger, which would run in a different scope.

Purpose of checking @@rowcount = 0 in a trigger?

The trigger fires for the statement being run. It will fire even if the table is empty, or if the statement affected no rows:

create table tr (i int);
go

create trigger g on tr after update
as 
print 'foo'
go

update tr set i = 2

@Muflix Update:

create table tr (i int);
go

create trigger g on tr after insert
as 
print 'foo'
go

insert into tr select * from tr;
go

As you see the trigger fires even if no rows were inserted.

How to implement rowcount properly in a trigger?

Triggers should not output messages, except error messages on failure (or perhaps for debugging). The client will get the normal rowcount message from the DML if the trigger has SET NOCOUNT ON.

So this should be simply:

CREATE OR ALTER TRIGGER tgr_limitTotalSupplier 
ON Suppliers_copy
AFTER INSERT, DELETE
AS
BEGIN
    DECLARE @R int;
    SET NOCOUNT ON;

    IF (SELECT COUNT(*) FROM Suppliers_copy) > 29
    BEGIN
        ROLLBACK TRANSACTION;
        RAISERROR ('Total suppliers cannot beyond 29. Your INSERT is aborted.', 16,1);
        RETURN;
    END

    IF (SELECT COUNT(*) FROM Suppliers_copy) < 25
    BEGIN
        ROLLBACK TRANSACTION;
        RAISERROR ('Total suppliers cannot below 25. Your DELETE is aborted.', 16,1);
        RETURN;
    END

END;

Get ROWCOUNT from INSTEAD OF TRIGGER

Some statements may change @@ROWCOUNT inside the trigger.

Statement

SELECT * FROM INSERTED WHERE COL1 < 5

executes and set @@ROWCOUNT to 1

Put statement

SET NOCOUNT ON;

then

IF NOT EXISTS (SELECT * FROM INSERTED WHERE COL1 < 5)
BEGIN
    SET NOCOUNT OFF;

    INSERT INTO dbo.ACCOUNT_CREDITS (COL1, COL2)
    SELECT COL1, COL2 from INSERTED
END

SQL Trigger @@ROWCOUNT

There is no @@ROWCOUNT in MySQL.
You can read this post to find ount how to replace it.

But you dont need that. Your trigger is for each row so it will fire for every updated row. (But that doesnt mean that your rows have changed. Just that they were updated by some statement.)

Scope of @@rowcount?

Granted, the article is for SQL Server 2000, but one would hope the scope doesn't change between versions. According to the article How triggers affect ROWCOUNT and IDENTITY in SQL Server 2000, @@ROWCOUNT will not be affected by triggers.

Specifically:

It’s safe to use @@ROWCOUNT in SQL Server 2000 even when there is a trigger on the base table. The trigger will not skew your results; you’ll get what you expect. @@ROWCOUNT works correctly even when NOCOUNT is set.

So if you update three rows, and the trigger updates five rows elsewhere, you'll get a @@ROWCOUNT of 3.

Also, from GBN's answer in SQL Server - is using @@ROWCOUNT safe in multithreaded applications?:

@@ROWCOUNT is both scope and connection safe.

In fact, it reads only the last statement row count for that connection and scope.

Will inserted and deleted record count always be equal in an update trigger?

I cant comment on MERGE as Steve has in his answer, but if an UPDATE is run on a table

UPDATE TableA SET Column1 = "ABC" WHERE Column1 = "DEF"

And an update trigger exists on TableA, then when the trigger fires, yes, the count of records in each of the Inserted & the Deleted tables will be the same, and will be equal to the number of rows affected by the update statement that was run.

Is @@ROWCOUNT after UPDATE reliably a measure of *matching* rows?

The documentation for @@ROWCOUNT is telling you the truth because 3 rows would be reliably affected as opposed to MySQL's ROW_COUNT().

not 2 (the number of rows modified by the UPDATE — one of the three
rows already had the value 1 for b).

For UPDATE it's not important if the new and previous values are identical. It simply does what its told to: finds data source, filters rows according to provided condition, and applies 'set' changes to filtered rows.

That's the way SQL Server works without any reservations. MySQL may work different. A row counting procedure is not a part of the SQL standard. So, you have to look before you leap for those kinds of artefacts every time you switch from one RDBMS to another.

Some triggers to see actual update behaviour:

CREATE TRIGGER [dbo].[trgFooForUpd]
ON [dbo].[Foo]
FOR UPDATE 
AS begin declare @id int;
      select @id = [a] from INSERTED;
      select * from INSERTED; end;
GO
CREATE TRIGGER [dbo].[trgFooAfterUpd]
ON [dbo].[Foo]
AFTER UPDATE 
AS print 'update done for ' + cast(coalesce( @@ROWCOUNT, -1) as varchar )+'rows'

---

Related Topics