SqlMel (9/23/2014)
Good question.This is something to consider when using triggers. If you need to execute the code inside the trigger only if it affects some rows, you can use the @@ROWCOUNT variable.
The following code will only insert 1 row in ABC_T1:
USE tempdb
GO
CREATE TABLE ABC_TriggerTest
(
ID INT NOT NULL
IDENTITY(1, 1)
, [Name] NVARCHAR(10)
)
GO
CREATE TABLE ABC_T1 ( ID INT )
GO
CREATE TRIGGER Trg_Test ON ABC_TriggerTest
FOR INSERT, UPDATE
AS
BEGIN
if @@ROWCOUNT > 0
BEGIN
INSERT INTO ABC_T1
VALUES ( '1' )
END
END
GO
INSERT INTO ABC_TriggerTest
SELECT 'First'
GO
INSERT INTO ABC_TriggerTest
SELECT [Name]
FROM ABC_TriggerTest
WHERE 1 = 2
--How many rows will be there for below statement
SELECT *
FROM ABC_T1
Alternatively, use the special Inserted table ,
USE tempdb
GO
CREATE TABLE ABC_TriggerTest
(
ID INT NOT NULL
IDENTITY(1, 1)
, [Name] NVARCHAR(10)
)
GO
CREATE TABLE ABC_T1 ( ID INT )
GO
CREATE TRIGGER Trg_Test ON ABC_TriggerTest
FOR INSERT, UPDATE
AS
BEGIN
if EXISTS(SELECT 1 FROM Inserted)
BEGIN
INSERT INTO ABC_T1
VALUES ( '1' )
END
END
GO
INSERT INTO ABC_TriggerTest
SELECT 'First'
GO
INSERT INTO ABC_TriggerTest
SELECT [Name]
FROM ABC_TriggerTest
WHERE 1 = 2
--How many rows will be there for below statement
SELECT *
FROM ABC_T1
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”