Trigger using inserted and deleted SQL Server 2005

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

Transact-SQL (2000)

Trigger using inserted and deleted SQL Server 2005

Author

Topic

budi
Starting Member

5 Posts

Posted - 2010-08-11 : 00:36:58

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

22859 Posts

Posted - 2010-08-11 : 07:34:32

[code]
...
FROM inserted
JOIN deleted
ON deleted.MyPK = inserted.MyPK
...
[/code]
If you do not have a column for [MyPK] that NEVER changes then I think this is going to be hard

budi
Starting Member

5 Posts

Posted - 2010-08-14 : 02:05:17

Thank Kristen, I use your recommedation to add Primary Key Field

Salam,
Budi

lb6688
Starting Member

18 Posts

Posted - 2010-08-15 : 14:04:38

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources