Trigger being called on all rows in database

开发者_运维百科I have written a trigger that I want to use for adding the date to a column in a record so that I can keep track of the insert of the item.

There are a large amount of inserts being called (about 20000) and I have noticed that the trigger will update all of the InsertDate columns associated with each item every time a new item is added. How can I make sure this happens to an item being inserted only one time.

My trigger is as follows:

 SET ANSI_NULLS ON
 SET QUOTED_INDENTIFIER ON
 GO


 CREATE TRIGGER [InsertDate_Item]
    ON [dbo].[ItemHolder]
    AFTER INSERT
    NOT FOR REPLICATION
 AS
 UPDATE ItemHolder SET InsertDate = GETDATE()

Any help will be much appreciated.

Thanks

You need to restrict rows to those inserted... using the virtual trigger table INSERTED

 CREATE TRIGGER [InsertDate_Item]
    ON [dbo].[ItemHolder]
    AFTER INSERT
    NOT FOR REPLICATION
 AS
 SET NOCOUNT ON
 UPDATE IH
 SET InsertDate = GETDATE()
 FROM
    ItemHolder IH
    JOIN
    INSERTED INS ON IH.keycol = INS.keycol
 Go

One thing: You'd be better adding a default to the table instead. No need for a trigger

ALTER TABLE ItemHolder ADD
CONSTRAINT DF_ItemHolder_InsertDate DEFAULT (GETDATE()) FOR InsertDate

If you are inserting records into a table, why don't you make the InsertDate field have a default value of GetDate()? That avoids the trigger altogether.

I'd go about what you're trying to do without a trigger. Just set the default value of the column to GetDate().

update ih set ih.insertdate = GetDate() from itemholder ih inner join inserted i on ih.itemholderid = i.itemholderid

change:

UPDATE ItemHolder SET InsertDate = GETDATE()

to:

UPDATE ItemHolder SET InsertDate = GETDATE() WHERE InsertDate IS NULL

The above will only set InsertDate if it is null and you still want to use the trigger. Of course, this is assuming the default value of InsertDate is null. If this is not the case let me know.