CODEWITHSUNDEEP
sql-servert-sqldatabase-trigger
Irfaan Wahid
Irfaan Wahid

Reputation: 11

SQL Server trigger inserting whole table rows, instead of new

I m making a mistake somewhere but can't figure out.

Scenario is this: Whenever a new row is inserted in table 'TestTrigger', the table TriggerInsert should get a value capturing time when a new row was inserted in TestTrigger.

The problem is, whenever the trigger hits, instead of just a new row, the whole table gets repopulated in TestTrigger, each time. I only want to capture, the new rows not the entire table getting inserted on each trigger.

Here is my trigger:

USE [irfaan]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Trigger [dbo].[InsertTriggerTest] on [irfaan].[dbo].[TestTrigger]
--For Insert
After insert
as
INSERT into TriggerInsert (CurrTime, IOFNum) SELECT (GetDate()), SONum   FROM TestTrigger

Please assist where I am going wrong.

Upvotes: 1

Views: 237

Answers (2)

Anthony K
Anthony K

Reputation: 2603

Your SELECT query is returning all rows from your source table (TestTrigger). Use the table inserted instead. It will contain just the rows that are being inserted into TestTrigger instead of all of the rows as you are currently getting.

INSERT into TriggerInsert (CurrTime, IOFNum) SELECT (GetDate()), SONum   FROM Inserted

Upvotes: 1

marc_s
marc_s

Reputation: 754983

If you want to look at just the rows that were inserted, you must use the Inserted pseudo table - not the dbo.TestTrigger base table....

ALTER TRIGGER dbo.InsertTriggerTest
ON irfaan.dbo.TestTrigger
FOR INSERT
AS 
   INSERT INTO TriggerInsert (CurrTime, IOFNum) 
       SELECT GETDATE(), SONum   
       FROM Inserted

Upvotes: 1

Related Questions