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
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?"