SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Trigger using inserted and deleted SQL Server 2005
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

budi
Starting Member

Indonesia
5 Posts

Posted - 08/11/2010 :  00:36:58  Show Profile  Reply with Quote
Hi All,

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

Salam,
Budi

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/11/2010 :  07:34:32  Show Profile  Reply with Quote

...
FROM inserted
    JOIN deleted
        ON deleted.MyPK = inserted.MyPK
...

If you do not have a column for [MyPK] that NEVER changes then I think this is going to be hard

Edited by - Kristen on 08/11/2010 07:34:54
Go to Top of Page

budi
Starting Member

Indonesia
5 Posts

Posted - 08/14/2010 :  02:05:17  Show Profile  Reply with Quote
Thank Kristen, I use your recommedation to add Primary Key Field

Salam,
Budi
Go to Top of Page

lb6688
Starting Member

18 Posts

Posted - 08/15/2010 :  14:04:38  Show Profile  Reply with Quote
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?"

Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.33 seconds. Powered By: Snitz Forums 2000