Solved

Trigger help - doing a select and only updating these records

Posted on 2014-09-09
10
289 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40311502
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
ID: 40311508
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 34

Expert Comment

by:ste5an
ID: 40311571
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:websss
ID: 40312120
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 34

Expert Comment

by:ste5an
ID: 40312304
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40312444
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
ID: 40316621
this worked first time!
0
 

Author Comment

by:websss
ID: 40316666
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:Scott Pletcher
ID: 40318244
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
ID: 40318604
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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

707 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