Solved

SQL Server 2008 Multirow update query trigger

Posted on 2012-04-05
2
315 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

785 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