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
Solved

Trigger help - doing a select and only updating these records

Posted on 2014-09-09
10
286 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
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

860 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