I need help to make trigger in SQL Server 2005, I have 2 tables, tb_component_master and tb_comp_by_model. I wanted if we update CompCode in tb_component_master, the trigger will automatically update CompCode column in tb_comp_by_model which have the same CompCode with new updated CompCode . As I know if we run UPDATE DML, old record will be saved in temp table 'deleted' and new record will be saved in 'inserted'. My script as below:
USE [mantis] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[tgg_change_compcode] ON [dbo].[tb_component_master] AFTER UPDATE AS BEGIN SET NOCOUNT ON;
UPDATE dbo.tb_comp_by_model SET dbo.tb_comp_by_model.CompCode = inserted.CompCode FROM inserted WHERE dbo.tb_comp_by_model.CompCode = deleted.CompCode
END
but I always get warning massage: Msg 4104, Level 16, State 1, Procedure tgg_change_compcode, Line 7 The multi-part identifier "deleted.CompCode" could not be bound.
What's wrong with my script? Can someone help me...Thanks before
Not to hijack this thread, Kristen, I posted a question in a very old thread on reporting changes on base on your audit trigger (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52356) "budi" may find it helpful as well as eventually, someone (boss?) will ask you "can you should me who changed where on what?"
Trigger using inserted and deleted SQL Server 2005
Posted - 08/11/2010 : 00:36:58
