Solved

SQL 2008 Update Trigger

Posted on 2010-08-15
6
594 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
ID: 33440883
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
ID: 33441175
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
ID: 33441317
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 8

Author Comment

by:Leo Torres
ID: 33441358
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
ID: 33441581
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
ID: 33441604
Glad you got it and I could help.

Best regards and happy coding,
Kevin
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

930 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