Solved

SQL 2008 Update Trigger

Posted on 2010-08-15
6
593 Views
Last Modified: 2012-05-10

I need to make a trigger that updates only rows that have changed

Table 1 Columns
ID, GroupGameID

Table 2 Columns
ID, GroupGameID


The 2 tables are matched by ID.. I need to trigger to update GroupGameID value in table 2 with changed value in table 1
0
Comment
Question by:Leo Torres
  • 3
  • 3
6 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
ltorres321,

You can use the DELETED and INSERTED tables to compare the values to tell when there has actually been a change to the GroupGameID field. You can also in newer versions of SQL use IF UPDATE(GroupGameID).
CREATE TRIGGER trg_Tbl1_UpdateTbl2

ON Tbl1

FOR UPDATE

AS

IF UPDATE(GroupGameID)

BEGIN

   UPDATE tto

   SET tto.GroupGameID = tfr.GroupGameID

   FROM Tbl2 tto

   INNER JOIN inserted tfr

      ON tfr.ID = tto.ID;

END

GO

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
Got this error

Msg 207, Level 16, State 1, Procedure tr_CreateGameDetail, Line 57
Invalid column name 'GameID'.

Not sure how insert is supposed to work here if this is supposed to be an update

 IF (UPDATE (GroupGameID))

   BEGIN

      UPDATE 

         dbo.GI_PlayerGameDetail 

	SET	pd.GroupGameID = i.GroupGameID

		FROM dbo.GI_PlayerGameDetail pd

		INNER JOIN inserted i

			ON i.GameID = pd.GameID



		End		

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
Is there an actual GameID column ? You said ID before.
IF (UPDATE (GroupGameID))

BEGIN

   UPDATE pd

   SET pd.GroupGameID = i.GroupGameID

   FROM dbo.GI_PlayerGameDetail pd

   INNER JOIN inserted i

      ON i.ID = pd.ID -- use proper ID columns here

END

Open in new window

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
Actually the actual Column is GameID i just used ID for the question
I am getting the error on line 8
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
Got thanks, FYI it was a the link one of the fields was NULL and no match was being made.. Thanks
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Glad you got it and I could help.

Best regards and happy coding,
Kevin
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now