trigger - wait 30 secs before processing

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?
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Where's the data are coming from?
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Have you tried

WAITFOR DELAY '00:00:30'

?
0
HuaMin ChenProblem resolverCommented:
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
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:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
How can you be sure the data will be there after the delay time?
0
websssAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
websssAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
Triggers are always bad to debug. If you have an issue will be a lot of trouble to track the issue with triggers.
0
websssAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
websssAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
websssAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
>> 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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
websssAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
websssAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
websssAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
websssAuthor Commented:
thanks guys, very insightful, have learnt a lot about WAITFOR today!
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.