I am running the belove code and expecting to see 2 affected rows , but what i see is that 3 rows are affected . HOW ?
I keep the exapmle simple :
I have a table :
<code>CREATE TABLE MyTable (
Column1 VARCHAR(50),
Column2 INT,
Column3 INT
);
</code>
<code>CREATE TABLE MyTable (
Column1 VARCHAR(50),
Column2 INT,
Column3 INT
);
</code>
CREATE TABLE MyTable (
Column1 VARCHAR(50),
Column2 INT,
Column3 INT
);
And a trigger :
<code>ALTER TRIGGER [MyTrigger]
ON [MyTable]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO [MyTable]
(Column1, Column2, Column3)
SELECT
i.Column1,
i.Column2,
CASE
WHEN i.Column3 < 100 THEN 20
ELSE i.Column3
END
FROM inserted i;
END;
</code>
<code>ALTER TRIGGER [MyTrigger]
ON [MyTable]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO [MyTable]
(Column1, Column2, Column3)
SELECT
i.Column1,
i.Column2,
CASE
WHEN i.Column3 < 100 THEN 20
ELSE i.Column3
END
FROM inserted i;
END;
</code>
ALTER TRIGGER [MyTrigger]
ON [MyTable]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO [MyTable]
(Column1, Column2, Column3)
SELECT
i.Column1,
i.Column2,
CASE
WHEN i.Column3 < 100 THEN 20
ELSE i.Column3
END
FROM inserted i;
END;
Another table :
<code>CREATE TABLE MyTable2 (
Column1 VARCHAR(50),
Column2 INT,
Column3 INT,
ACTION VARCHAR(20)
);
</code>
<code>CREATE TABLE MyTable2 (
Column1 VARCHAR(50),
Column2 INT,
Column3 INT,
ACTION VARCHAR(20)
);
</code>
CREATE TABLE MyTable2 (
Column1 VARCHAR(50),
Column2 INT,
Column3 INT,
ACTION VARCHAR(20)
);
Another trigger :
<code> ALTER TRIGGER [MyTrigger_2]
ON [MyTable]
FOR INSERT
AS
BEGIN
DECLARE @myAction_Inserted NVARCHAR(50)
SET @@myAction_Inserted ='INSERTED'
INSERT INTO MyTable2
(Column1, Column2, Column3,ACTION)
SELECT
i.Column1,
i.Column2,
i.Column2,
@myAction_Inserted
FROM inserted i;
</code>
<code> ALTER TRIGGER [MyTrigger_2]
ON [MyTable]
FOR INSERT
AS
BEGIN
DECLARE @myAction_Inserted NVARCHAR(50)
SET @@myAction_Inserted ='INSERTED'
INSERT INTO MyTable2
(Column1, Column2, Column3,ACTION)
SELECT
i.Column1,
i.Column2,
i.Column2,
@myAction_Inserted
FROM inserted i;
</code>
ALTER TRIGGER [MyTrigger_2]
ON [MyTable]
FOR INSERT
AS
BEGIN
DECLARE @myAction_Inserted NVARCHAR(50)
SET @@myAction_Inserted ='INSERTED'
INSERT INTO MyTable2
(Column1, Column2, Column3,ACTION)
SELECT
i.Column1,
i.Column2,
i.Column2,
@myAction_Inserted
FROM inserted i;
Now whn I run this :
<code>DECLARE @sql NVARCHAR(MAX) = '';
SET @sql = 'INSERT INTO MyTable (Column1, Column2, Column3)
VALUES (''Test1'', 10, 105)';
EXEC sp_executesql @sql;
</code>
<code>DECLARE @sql NVARCHAR(MAX) = '';
SET @sql = 'INSERT INTO MyTable (Column1, Column2, Column3)
VALUES (''Test1'', 10, 105)';
EXEC sp_executesql @sql;
</code>
DECLARE @sql NVARCHAR(MAX) = '';
SET @sql = 'INSERT INTO MyTable (Column1, Column2, Column3)
VALUES (''Test1'', 10, 105)';
EXEC sp_executesql @sql;
It outputs :
<code>(1 row affected)
(1 row affected)
(1 row affected)
</code>
<code>(1 row affected)
(1 row affected)
(1 row affected)
</code>
(1 row affected)
(1 row affected)
(1 row affected)
Shouldn’t be just 2 affetcted rows, one in MyTable
an another in MyTable2