Solved

trigger - wait 30 secs before processing

Posted on 2014-11-03
27
186 Views
Last Modified: 2014-11-03
Hi
I have sql 2014
I have a trigger which runs on a DB to updates various tables


I have found an issue that we need to wait between 4-30 seconds for data to come in, so want part of the trigger which updates a table, to fire after waiting for this time

is it possible to put a WAIT delay on part of a trigger ? before it updates this table?
0
Comment
Question by:websss
  • 10
  • 9
  • 7
  • +1
27 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40419119
Where's the data are coming from?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40419120
Have you tried

WAITFOR DELAY '00:00:30'

?
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 40419124
You can use a job for that. A job is a specified series of operations performed sequentially by SQL Server Agent. A job can perform a wide range of activities, including running Transact-SQL scripts, command-line applications, Microsoft ActiveX scripts, Integration Services packages, Analysis Services commands and queries, or Replication tasks. Jobs can run repetitive tasks or those that can be scheduled, and they can automatically notify users of job status by generating alerts, thereby greatly simplifying SQL Server administration.

Read
http://blog.sqlauthority.com/2009/01/03/sql-server-time-delay-while-running-t-sql-query-waitfor-introduction/
http://msdn.microsoft.com/en-us/library/vstudio/938d9dz2(v=vs.100).aspx
0
 

Author Comment

by:websss
ID: 40419147
The data is coming from the Message Queue which a console app is picking up and inserting into a Table

the table then runs a trigger to insert part of that inserted data into another table.... this is where i need a WAIT as some other data could be a few seconds behind it

it a WAIT the best way to do this?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40419149
I believe so - that's what WAITFOR (one word) is for.

See http://msdn.microsoft.com/en-us/library/ms187331.aspx for more details.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40419151
How can you be sure the data will be there after the delay time?
0
 

Author Comment

by:websss
ID: 40419153
How can you be sure the data will be there after the delay time?

because its buffered and take a few secs to get processed by  some other code... the delay in the lookups are the root of all the issues
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40419227
Be aware that if you are in a transaction the table will be locked during all the Wait period.
I will recommend you to find another solution. Something more elegant.
0
 

Author Comment

by:websss
ID: 40419230
Thanks Vitor

The tigger is updating another table, and thats it
there maybe multiple versions of the trigger running at the same time...will this be an issue?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40419236
If a trigger is on a WAITFOR, and part of the main table is locked, then if the main table receives another UPDATE, then that transaction will have to wait until the WAITFOR and the rest of the trigger code is completed.

Would that be an issue if this second UPDATE has to wait for up to 30 seconds?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40419241
Triggers are always bad to debug. If you have an issue will be a lot of trouble to track the issue with triggers.
0
 

Author Comment

by:websss
ID: 40419245
Thanks

Yes it would be an issue!
each trigger would update only 1 row

would a waitFOR Lock the WHOLE table then, an no other trigger could process until its finished waiting? or is it just for that specific row?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40419250
It can lock a row but depending on the quantity of rows to be updated the engine can escalate to page locking and depending on many situations it can escalate to a full table lock (long running query can be one of these situations).
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:websss
ID: 40419259
At present it will never be more than 10 records waitFOR at a time

However, it could potentially be hundreds of records updating their row at any one time

... what can SQL handle if its just ROW level locking?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40419263
What about a completely different approach.

Can you do a stored procedure which imports how you want to change your table, do the WAITFOR, and then do the changes.

That way, you don't have a transaction active (I hope) during the WAITFOR.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40419270
You can use locking hints to try to force a row lock but the engine optimizer can always override the hint if it thinks that it will be necessary. Check more for table hints.
0
 

Author Comment

by:websss
ID: 40419277
Hi Philip

Ok, let me expand

Its a company vehicle tracking app
When we get updates from the lorry, we store the info in tblRawData

Then a trigger runs and checks if its ignition on/off
If its either of these it inserts a record in the Trip table (for ignitionOn) and for IgnitionOff it updates the same row
however, sometimes we get Ignition Off before Ignition on when the buffered data comes in

So in the instance of Ignition OFF, i'd like to wait 30 secs as this will ensure ignition ON is already in the TripTable, so i can update that row with Ignition OFF

So, using your comment above, i'm assuming I do this

In the trigger, when i get the the bit that detects if its Ignition OFF, call a SP and pass in the data
In that SP I would start it with a WAITFOR 30 secs, and then Update the TripTable

Is this correct?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40419283
>> When we get updates from the lorry, we store the info in tblRawData.

How - this is the crucial component. How do you do this?

What happens if you get multiple updates?

It sounds to me that you don't need a trigger.

Maybe it should be more like this:

1. Receive update from the lorry.
2. Store info in tblRawData. END OF TRANSACTION.
3. Pass new row numbers of tblRawData into a Stored Procedure.
4. Update Ignition ON, and update tblRawData with a boolean "True" for those rows. END OF TRANSACTION.
5. Wait 30 seconds.
6. Update Ignition OFF, and update tblRawData with a boolean "True" for those rows. END OF TRANSACTION.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40419284
In the trigger, when i get the the bit that detects if its Ignition OFF, call a SP and pass in the data
 In that SP I would start it with a WAITFOR 30 secs, and then Update the TripTable
This solution won't help because you are calling the SP from the trigger, so you'll keep the issue of the lock that will be released only after the execution of the SP.

You need to find a better place to place the trigger.
By now when the trigger is fired?
If you can post part of the code will be helpful as well.
0
 

Author Comment

by:websss
ID: 40419304
The Data is pulled from a message queue, and is then inserted to the raw table
we can get an update between every 1 - 60 seconds for one lorry, there are about 500 lorries so we get lots of info coming in at same time

each update is a different type
I.e. position update, cargo door open, ignition on, ignition off, harsh breaking, harsh acceleration etc

the trigger is below:
As you can see we use data within the trigger to update different tables

I figured a trigger would be more efficient ?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[trgTrackDataInsert]
on [dbo].[wlt_tblTrackingData_Raw_AT] after INSERT

as

declare @ipkMSMQTrackId as numeric(18,0)
declare @userId as numeric(10,0)=0
declare @ifkGroupMID as numeric(10,0)
declare @vSequenceID as varchar(50)
declare @vUnitID as varchar(50)
declare @vGPSDateTime as varchar(50)
--declare @vRTCDateTime as varchar(50)
declare @vPositionSendingDateTime as varchar(50)
declare @vLatitude as varchar(50)
declare @vLongitude as varchar(50)
declare @vHeading as varchar(50)
declare @vReportID as varchar(50)
declare @vReportIDCommon as varchar(50)
declare @vOdometer as varchar(50)
declare @vHDOP as varchar(50)
declare @vInputStatus as varchar(50)
declare @vVehicleSpeed as varchar(50)
declare @vOutputStatus as varchar(50)
declare @vAnalogInputValue as int
declare @vDriverID as varchar(50)

declare @vFirstTempSensorValue as varchar(50)
declare @vSecondTemperatureSensorValue as varchar(50)
declare @vTextMessage as varchar(500)
declare @rcvDate as varchar(20)
declare @PositionTime as varchar(20)
declare @dEntryDate as datetime
declare @vTextPos as varchar(500)
declare @Ename as nvarchar(100)
declare @vTripStatus as nvarchar(20)=''
DECLARE @nStartEventInstPrev geography
DECLARE @nStartEventInstNext geography
declare @vTempLatitude as varchar(50)
declare @vTempLongitude as varchar(50)
declare @IsAlert as varchar(50) ='0'
declare @iSoftwarePriority int
declare @bIgnitionStatus bit
declare @bEngineStatus bit
declare @ipkDriverID as int
declare @cInput1 as char(1)
declare @cInput2 as char(1)
declare @cInput3 as char(1)
declare @cInput4 as char(1)
declare @vAnalog1 as varchar(20)
declare @vRoadSpeed varchar(10)=''

--declare @vFirstTempSensorValue as varchar(20)
--declare @SecondTempSensorValue as varchar(20)

begin
		select @ipkMSMQTrackId=max(ipkMSMQTrackId) from wlt_tblTrackingData_Raw_AT




		select 
			@vSequenceID=vSequenceID, 
			@vUnitID=vUnitID,
			@vGPSDateTime=vGPSDateTime,
			--@vRTCDateTime=vRTCDateTime,
			@vPositionSendingDateTime=vPositionSendingDateTime,
			@vLatitude=vLatitude,
			@vLongitude=vLongitude,
			@vHeading=vHeading,
			@vReportID=vReportID,
			@vOdometer=vOdometer,
			@vHDOP=vHDOP,
			@vInputStatus=vInputStatus,
			@vVehicleSpeed=vVehicleSpeed,
			@vOutputStatus=vOutputStatus,
			@vAnalogInputValue=vAnalogInputValue,
			@vDriverID=vDriverID,
			@vFirstTempSensorValue=vFirstTempSensorValue,
			@vSecondTemperatureSensorValue=vSecondTemperatureSensorValue,
			@vTextMessage=vTextMessage,
			@dEntryDate=dEntryDate,
			@vTextPos=vTextPos,
			@IsAlert= IsAlert,
			@iSoftwarePriority = iSoftwarePriority,
			@bIgnitionStatus = bIgnitionStatus,
			@bEngineStatus = bEngineStatus
			,@vRoadSpeed = vRoadSpeed

        from wlt_tblTrackingData_Raw_AT where ipkMSMQTrackId=@ipkMSMQTrackId
    
		--Get the common Report ID
		  select @vReportIDCommon= ifkCommonEventLookupId from wlt_tblEvents_CommonEvents_Lookup_DeviceManufacturer
		  Where vEventId = @vReportID and vDeviceType = 'AT'
			
					Select @ipkDriverID = IIF(@vDriverID IS NOT NULL OR @vDriverID <> '' 
					,  (Select top(1) ipkDriverID from wlt_tblAssets_Driver_Encrypted inner join wlt_tblAssets_Driver  on wlt_tblAssets_Driver_Encrypted.ifkAssets_Driver_Id =wlt_tblAssets_Driver.ipkDriverID 
						Where vIButtonTagEncrypted = @vDriverID and bIsDeleted = 0 and bStatus = 1)
					, NULL) 


		select @vSequenceID =  (SELECT IDENT_CURRENT('wlt_tblTrackingData_Raw_AT'))

	 SET XACT_ABORT OFF;
            

	 begin try


		                       
       set @rcvDate= (SUBSTRING(@vGPSDateTime,1,4)+'/'+ (SUBSTRING(@vGPSDateTime,5,2))+'/'+(SUBSTRING(@vGPSDateTime,7,2))
         +' '+ (SUBSTRING(@vGPSDateTime,9,2))+':'+ (SUBSTRING(@vGPSDateTime,11,2)) +':'+ (SUBSTRING(@vGPSDateTime,13,2)))

       set @PositionTime=(SUBSTRING(@vPositionSendingDateTime,1,4)+'/'+ (SUBSTRING(@vPositionSendingDateTime,5,2))+'/'+(SUBSTRING(@vPositionSendingDateTime,7,2))
         +' '+ (SUBSTRING(@vPositionSendingDateTime,9,2))+':'+ (SUBSTRING(@vPositionSendingDateTime,11,2)) +':'+ (SUBSTRING(@vPositionSendingDateTime,13,2)))


	
			   INSERT INTO tblCommonTrackingData 
				(	vSequenceID
					,vpkDeviceID
					,dGPSDateTime
					,vLongitude
					,vLatitude
					,vHeading
					,vReportID
					,vOdometer
					,vVehicleSpeed
					,vTextMessage
					,ifkDriverID
					,bIsIgnitionOn
					,bIsEngineOn
					,vEventName
					,FuelVoltage
					,IsAlert
					,cInput1
					,cInput2
					,cInput3
					--,cInput4
					,vAnalog1
					,vTempSensor1
					,vTempSensor2
					,iHdop
					,iSoftwarePriority
					,vRoadSpeed
					
					)  
			   VALUES(
				   @vSequenceID
				   ,@vUnitID
				   ,@rcvDate
				   ,@vLongitude
				   ,@vLatitude
				   ,cast(@vHeading AS INT)
				   ,cast(@vReportIDCommon AS INT)
				   
				   --,@vOdometer
				   --,convert(numeric,(@vOdometer * 0.1))
				   --,convert(float,((Cast(  @vOdometer as numeric)) * 0.1))
				   ,convert(numeric,@vOdometer) * 0.1

				   ,cast(@vVehicleSpeed AS INT)
				   ,@vTextPos
				   ,@ipkDriverID
				   ,@bIgnitionStatus 
				   ,@bEngineStatus
				   --,cast(@vInputStatus AS BIT)
				   ,(select vEventName FROM wlt_tblEvents_CommonEvents_Lookup WHERE ipkCommonEventLookupId=cast(@vReportIDCommon AS INT))
				   ,convert(FLOAT,(@vAnalogInputValue * 0.001))
				   ,@IsAlert
				   ,(SUBSTRING(@vInputStatus,1,1))
				   ,(SUBSTRING(@vInputStatus,2,1))
				   ,(SUBSTRING(@vInputStatus,3,1))
				   --,(SUBSTRING(@vInputStatus,4,1))
				   ,@vAnalog1
				   ,@vFirstTempSensorValue
				   ,@vSecondTemperatureSensorValue
				   ,@vHDOP
				   ,@iSoftwarePriority
				   ,@vRoadSpeed 
			   )            
	     

	
	   
  


			IF @vReportIDCommon='7' 
			 BEGIN   

				 Insert INTO dbo.tblTrip_Master(dStartTime,nStartEventInst,vpkDeviceID,DriverIDNo, ifkDriverID)VALUES(@rcvDate,(SELECT IDENT_CURRENT('wlt_tblTrackingData_Raw_AT')),@vUnitID,@vDriverID, @ipkDriverID)
				 set @vTripStatus='Starts'

			 END     

--this updates trip table with END trip and completes the trip row                  
			else IF @vReportIDCommon='8' 
			 BEGIN
				 if exists(select top(1) nStartEventInst from tblTrip_Master where vpkDeviceID=@vUnitID) 
				   BEGIN
			

						update dbo.tblTrip_Master set dEndTime=@rcvDate,nEndEventInst=(SELECT IDENT_CURRENT('wlt_tblTrackingData_Raw_AT')), ifkDriverID=@ipkDriverID WHERE ipkTripID=(SELECT TOP(1) ipkTripID FROM tblTrip_Master where vpkDeviceID=@vUnitID ORDER BY dStartTime DESC)
						set @vTripStatus='Ends'
				  END
       
 
			 END 

	  end try

	 begin catch

	 end catch
end

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40419318
I don't know how that would cope with the scenario of having to wait thirty seconds that you were talking about before.

At the moment, you will have thousands of updates every minute. Do you need immediate, per second, analysis, or can it lag one or two minutes behind?

If it can, then you could have an SQL Server Agent Job that runs every thirty seconds, which has two steps.

1. Update all ignition ON that has not been marked (and mark those you have just updated).
2. Update all ignition OFF that is at least 30 seconds old that have not already been done (and mark those you have just updated, so they will not be done again).

That way, no need to wait for anything.
0
 

Author Comment

by:websss
ID: 40419325
It needs to be pretty instant

however, the ignition off isn't that critical, so i could remove this from the trigger, and do it elsewhere

would a 30 sec agent be suitable?
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 300 total points
ID: 40419331
It depends on what you need.

You've said that "ON" needs to be instant, so the trigger seems appropriate.

You've said that "OFF" isn't that critical, so if you can move it elsewhere, it should reduce blocking, not just for the updating, but also for the reading.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40419334
I saw you code and that's a INSERT trigger, meaning that ALL table are locked during the process. So [wlt_tblTrackingData_Raw_AT], tblCommonTrackingData and tblTrip_Master (in the case @vReportIDCommon='7') will be locked during the process.

Phillip's idea for the job isn't bad at all if you can manage to update the records after.
0
 

Author Comment

by:websss
ID: 40419364
Thanks both

Vitor - you mentioned that all tables are locked, is that really bad?

So would this solution be suitable ?

1. run an agent every 30 secs
2. look for data older that 30 secs in the TRIP table that doesn't have an Ignition Off
3. search for the corresponding Ignition Off in RAW table
4. Update TRIP table with the IgnitionOff ID
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 200 total points
ID: 40419372
Vitor - you mentioned that all tables are locked, is that really bad?
Yes if you going to use WAITFOR DELAY.

So would this solution be suitable ?
 1. run an agent every 30 secs
 2. look for data older that 30 secs in the TRIP table that doesn't have an Ignition Off
 3. search for the corresponding Ignition Off in RAW table
 4. Update TRIP table with the IgnitionOff ID
At least it's more easy to debug. I think it's more elegant than WAITFOR DELAY solution.
0
 

Author Closing Comment

by:websss
ID: 40419582
thanks guys, very insightful, have learnt a lot about WAITFOR today!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
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.

758 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

21 Experts available now in Live!

Get 1:1 Help Now