Solved

SQL Server 2008 Multirow update query trigger

Posted on 2012-04-05
2
314 Views
Last Modified: 2012-04-06
I need help - I am trying to create a trigger to do the following:

When I run the query shown below all the records in Tbl_Points are set to the first value it finds and thus not doing a muti-record update

Any assistance on what I am doing wrong is most welcome



BEGIN

use xxdatabase

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

-- I need to update the Tbl_Points with the Trans_Date from the Tbl_Transaction_Asset_Latest

update Tbl_Points

-- The field to update is in table Tbl_Points field PDA_Notes5 and needs to use
-- Tbl_Transaction_Asset_Latest.Trans_Date

SET PDA_Notes5 = Tbl_Transaction_Asset_Latest.Trans_Date

 FROM Tbl_Transaction_Asset_Latest

--get records from tbl_transaction_Latest

 where (Tbl_Transaction_Asset_Latest.Point_ID = Tbl_Points.Point_ID and Clone_Id = 'C000000018'  AND EXISTS ((SELECT Tbl_Points.Point_ID FROM Tbl_Points WHERE Point_ID = Tbl_Transaction_Asset_Latest.Point_ID))
0
Comment
Question by:infotron
2 Comments
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 37815284
That exists-part seems to repeat the join you camouflaged in the where
update p
-- The field to update is in table Tbl_Points field PDA_Notes5 and needs to use
-- Tbl_Transaction_Asset_Latest.Trans_Date
SET p.PDA_Notes5 = t.Tbl_Transaction_Asset_Latest.Trans_Date 
FROM Tbl_Points  as p
inner join Tbl_Transaction_Asset_Latest as t on t.Tbl_Transaction_Asset_Latest.Point_ID = p.Point_ID
--get records from tbl_transaction_Latest
 where  t.Clone_Id = 'C000000018'  

Open in new window

<<When I run the query shown below all the records in Tbl_Points are set to the first value it finds and thus not doing a muti-record update>>
all the records --> then it is a multi-record update isn't it?

<<I need help - I am trying to create a trigger to do the following>>
Don't know where's your problem.

See here example on a multi-row trigger , look for the join with 'inserted' which represents the new values , use UPDATE() to test if the column is changed.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113917
Here for tutorial
 http://msdn.microsoft.com/en-us/library/ms189799(v=sql.105).aspx
0
 

Author Closing Comment

by:infotron
ID: 37815950
Thanks a ton - the query did not like the Tbl_Transaction_Asset_Latest but otherwise it worked perfectly.

The set command below
SET p.PDA_Notes5 = DATEADD(day,7,t.Trans_Time)
returns Oct  5 2012  1:21PM

Is there a way to convert this to mm/dd/yyyy on the fly?
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

929 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

20 Experts available now in Live!

Get 1:1 Help Now