Improve company productivity with a Business Account.Sign Up

x
?
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

606 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