Solved

Trigger help - doing a select and only updating these records

Posted on 2014-09-09
10
276 Views
Last Modified: 2014-09-11
Hi

I want to do the following
- Only run trigger code when the table has been UPDATED

The code would go something like this
If A record was just UPDATED {
Select * tblTrip_Master where dEndTime is Not Null AND nEndEventInst is Not Null and dStartDate is less than 2 weeks old
{
  run my update SQL on these records
}
}

Could someone help me getting started with the trigger code for this?
0
Comment
Question by:websss
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
create trigger myTrigger
on myTable
for update
as
Select * tblTrip_Master where dEndTime is Not Null AND nEndEventInst is Not Null and dStartDate > getdate() - 14
{
  run my update SQL on these records
}
}

Open in new window

0
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
If you wanted a true "less than 2 weeks old" you'd need to ignore the time element. I do that by this:

Select * tblTrip_Master where dEndTime is Not Null AND nEndEventInst is Not Null and dStartDate > cast(convert(nvarchar(10), getdate(), 103) as datetime) - 14

Open in new window


Converting to nvarchar(10) strips the time element off by truncating it and then converting it back to a datetime makes the time portion go to midnight of that day. If you don't cater for that you may miss some records that fall in or out of the filter because of the time of day they were set.
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
Please rephrase your question.

But it when you need to update all affected rows, when a external condition is true, then it should look like:

CREATE TRIGGER myTrigger ON myTable
    FOR UPDATE
AS
    IF EXISTS ( SELECT  *
                FROM    tblTrip_Master
                WHERE   dEndTime IS NOT NULL
                        AND nEndEventInst IS NOT NULL
                        AND dStartDate > GETDATE() - 14 )
        BEGIN
            UPDATE  T
            SET     yourColumn = 123
            FROM    myTable T
                    INNER JOIN INSERTED I ON T.primaryKeyColumns = I.primaryKeyColumns;
        END;

Open in new window


Caveat: Rows are affected by an UPDATE operation, when a column value was set. This does not mean, that the newly set value is different from the old value. For a real update you need to test the values explicitly. E.g.
UPDATE  T
SET     yourColumn = 123
FROM    myTable T
        INNER JOIN DELETED D ON T.primaryKeyColumns = D.primaryKeyColumns
        INNER JOIN INSERTED I ON T.primaryKeyColumns = I.primaryKeyColumns
WHERE   I.columns != D.columns;

Open in new window

0
 

Author Comment

by:websss
Comment Utility
Thanks all

I've started using ste5an first bit of code

I have this

CREATE TRIGGER trgUpdateAverageSpeeds ON [dbo].[tblTrip_Master]
    FOR UPDATE
AS
    IF EXISTS ( SELECT  *
                FROM    tblTrip_Master
                WHERE   dEndTime IS NOT NULL
                        AND nEndEventInst IS NOT NULL
                        AND dStartTime > GETDATE() - 14 
						AND iAverageSpeed is NULL
						)
        BEGIN
			
			--for each of the above

			declare @StartSequenceID int = select startid from sometable
			declare @EndSequenceID int
			declare @OdoStart int 
			declare @odoEnd int
			declare @MaxSpeed int 
			declare @TimeStart date  
			declare @TimeEnd date 

                        Update table set xx = xx where ID = current Record

Open in new window


I wanted some way of efficiently cycling through each record from the first IF EXISTS statement and updating each record as it cycles through the results

could someone help with this please?
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
You normally don't need to "cylce" (or rbar) through it. Use a set-based approach using the special tables INSERTED and DELETED as I did in my example.

It depends also on your kind of update logic. Please post a complete and concise example.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
CREATE TRIGGER tblTrip_Master__TRG_UPD
ON tblTrip_Master
AFTER UPDATE
AS
SET NOCOUNT ON;
UPDATE tm
SET data_col = new_value /*, ...*/
FROM tblTrip_Master tm
INNER JOIN inserted i ON
    i.key_col = tm.key_col --AND i.key_col2 = tm.key_col2
WHERE
    i.dEndTime IS NOT NULL AND
    i.nEndEventInst IS NOT NULL AND
    i.dStartDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 13, 0)
0
 

Author Closing Comment

by:websss
Comment Utility
this worked first time!
0
 

Author Comment

by:websss
Comment Utility
Got a slight glitch

This is the working code which updates the iMaxSpeed of the correct Row

However, I need to do a calculation to work out the average speed
Do do this I need to know the dEndTime from tblTripMaster of the Row that was just Updated

CREATE TRIGGER tblTrip_Master__TRG_UPD
ON tblTrip_Master
AFTER UPDATE
AS
SET NOCOUNT ON;



UPDATE tm
    SET iMaxSpeed = 70 /*, ...*/


	--declare @TimeEnd date = tm.dEndTime
	-- here i need to know the rows current values for certain columns
	
FROM tblTrip_Master tm
INNER JOIN inserted i ON
    i.ipkTripID = tm.ipkTripID --AND i.key_col2 = tm.key_col2
WHERE
    i.dEndTime IS NOT NULL AND 
    i.nEndEventInst IS NOT NULL AND
    i.dStartTime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 13, 0)

Open in new window


Is this even possible?
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
The i.dEndTime is available to you.  Do you need to reset it?  If so, you can just add it to the SET statement, with the appropriate new value.
0
 

Author Comment

by:websss
Comment Utility
Got it thanks
I needed it about the update but it's not available there so I've managed to do it within the update....  Just makes reading it a bit more difficult but it's working Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

771 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

11 Experts available now in Live!

Get 1:1 Help Now