Trigger help - doing a select and only updating these records

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?
websssAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee SavidgeCommented:
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
Lee SavidgeCommented:
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
ste5anSenior DeveloperCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

websssAuthor Commented:
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
ste5anSenior DeveloperCommented:
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
Scott PletcherSenior DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
websssAuthor Commented:
this worked first time!
0
websssAuthor Commented:
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
Scott PletcherSenior DBACommented:
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
websssAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.