?
Solved

SQL Server 2008 Multirow update query trigger

Posted on 2012-04-05
2
Medium Priority
?
327 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 2000 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

593 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