Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Update trigger also fires on Insert (it shouldn't)

Author  Topic  emma Starting Member 1 Post Posted - 2001-04-06 : 12:54:24 Hi, I have a table called Profile and one called Profile_History, and I have an update trigger that should fire when any field in the Profile table is updated, and copy the previous values to the Profile_History. THis works, but it also works when the record is first inserted in the table, which I don't want (it makes some web pages that are dependant on the Profile_History table fall over) CREATE TRIGGER Profile_History ON dbo.FMG_Profile FOR UPDATE AS Insert Into Profile_History ([Code],[Name],[Type],[Address], [Status], [Last_Modified_By], [Last_Modified_Date], [Active]) Select [Code],[Name], [Type], [Address], [Status], [Last_Modified_By], [Last_Modified_Date], [Active] from Deleted Are there circumstances in which Update triggers ever fire on insert? Is there something wrong in the way the trigger is written? The [address] field is actually an id field that references an address table, does that make a difference??? When the original record is created, the address fields are inserted in the address table, the address id is retrieved, and that id is then put into the profile table alongside the other profile information. Does that make a difference, as far as the trigger is concerned??? When the trigger fires on insert, the addressid is set to 0 (and as far as I can tell, that's why the web pages fall over) I've created a similar Update trigger on the address table, and that one behaves as you'd expect (i.e doesn't fire on insert). Any help on this will be gratefully received. Thanks in advance!