Solved

SQL Server 2008 Multirow update query trigger

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

757 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

19 Experts available now in Live!

Get 1:1 Help Now