Solved

Trigger help - doing a select and only updating these records

Posted on 2014-09-09
10
288 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
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 33

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 33

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

726 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