Delaying Trigger
-
Hi Everyone,
I have a trigger executing when data is inserted into one of my tables.
The trigger affects so much data though that the procedure actually crashes.
Is there anyway to delay the time of the trigger before it is executed?
Thanks In Advance
-
Is there a way to make to trigger run faster?
Can we see the code?
-
BOL - WAITFOR { DELAY 'time' | TIME 'time' }
Not sure if that will work with a Trigger... (Remi has a very good point, you may want to look at how to increase the speed of the Trigger...).
I wasn't born stupid - I had to study.
-
It wouldn't solve the problem... the trigger still has to finish before giving the control back to the proc...
-
I think that you either mak it faster as it was already proposed or encapsulate that logic on a stored proc and call it from a job if at all possible
* Noel -
I have seen somewhere either in an article or a discussion thread on this site someone talking about "asynchronous sql execution"... Essentially they created a scheduled job using TSQL from a stored proc (in your case, from a trigger) that was scheduled for X seconds/minutes from now. Then so long as SQL Server Agent was running, the code would be executed and your transaction could be long ago completed
Have never tried this myself, but so long as the job doesn't take too long to create, it should be ok?
-
Doesn't mean it's the right thing to do... What if the trigger fails and needs to rollback everything (by design or by big problem). Just for the sake of data integrity, I'd be very relunctant to use that one (assuming I can't optimize the trigger).
-
I very much agree, just another tool in the belt that may come in handy.
-
How about minimizing the amount of work that the trigger does. Maybe write to a table the fact that something needs to be done with the appropriate information needed to do it. Then, during off hours run a job that can take that information and do the heavy lifting. It all depends upon what tasks you are asking your trigger to do.
Steve
-
The reason the trigger timesout is because of an stored procedure that i'm running.
Here is the code:
UPDATE tblForecastTemp SET hour1 = (SELECT tbltemp.hour1 FROM tbltemp
WHERE tblForecastTemp.[year] = tbltemp.[year] AND tblForecastTemp.[month]
= tbltemp.[month] AND tblForecastTemp.[day] = tbltemp.[day])
WHERE EXISTS (SELECT tbltemp.hour1 FROM tbltemp
WHERE tblForecastTemp.[year] = tbltemp.[year] AND tblForecastTemp.[month]
= tbltemp.[month] AND tblForecastTemp.[day] = tbltemp.[day]);
This update affects about 13,000 records. I was wondering if I can limit the amount of record updated based on a time restrict. For instance, only update tblForecastTemp if tbltemp records were inserted today.
Thanks for any advice.
-
Can you post the trigger code too?
This can be optimized a lot.. should stop timing out.
-
Here is the trigger code:
CREATE Trigger RollingDB ON tbltemp
AFTER insert
AS
EXECUTE dbo.UpdateTempNulls
EXECUTE dbo.rollingTable
EXECUTE dbo.InsertIntoForecast
EXECUTE dbo.UpdateForecast
Here is the code for the stored procedures:
Create Procedure UpdateTempNulls
AS
UPDATE tblTemp
SET hour1 = (hour2+hour24)/2
WHERE hour1 IS NULL
Create Procedure RollingTable AS
DECLARE @date datetime
SET @date = (SELECT TOP 1 [enersource\khanif].dateserial([year],[month],[day])FROM tblforecastdate
ORDER BY [enersource\khanif].dateserial([year],[month],[day]) DESC)
DECLARE @futdate datetime
SET @futdate = GetDate()
WHILE @date < DateAdd(year,2,@futdate
Begin
SELECT @date
SET @date = DateAdd(day,1,@date)
insert into tblforecastdate ([year],[month],[day])Values(Year(@date),Month(@date),Day(@date))
End
CREATE PROCEDURE InsertIntoForecast
AS
INSERT INTO tblForecastTemp ([year],[month],[day],hour1,hour2,hour3,hour4,hour5,
hour6,hour7,hour8,hour9,hour10,hour11,hour12,hour13,hour14,hour15,hour16,hour17,hour18,hour19,hour20,hour21,hour22,hour23,hour24)
SELECT [year],[month],[day],hour1,hour2,hour3,hour4,hour5,
hour6,hour7,hour8,hour9,hour10,hour11,hour12,hour13,hour14,hour15,hour16,hour17,hour18,hour19,hour20,hour21,hour22,hour23,hour24
FROM ForecastTemp WHERE dbo.dateserial([year],[month],[day]) >
(SELECT TOP 1 dbo.dateserial([year], [month], [day]) FROM tblforecasttemp
ORDER BY dbo.dateserial([year], [month], [day]) DESC)
ORDER BY dbo.dateserial([year],[month],[day])
The actual trigger doesn't timeout but I'm loading a csv file into a table through a VB.NET application. This is where the timeout occurs.
-
I don't even know where to begin. This is not the way a trigger should be coded. Open the books online and check this section : "create trigger"
You'll have exemples on how to use the inserted and deleted tables in updates and inserts operations.
Here's the exemple for selects :
SELECT @min_lvl = min_lvl,
@max_lvl = max_lvl,
@emp_lvl = i.job_lvl,
@job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id
JOIN jobs j ON j.job_id = i.job_id
I would strongly suggest that you drop all the procs and move their code in the trigger itself and updating only the necessary rows.
Then there's that insert problem... How do you transfer the cvs file from .net?
-
I don't see how you use the inserted table (if you need it at all)
If you don't need that just run the sp directly
oh and I am also interested in knowing how you perform that import?
* Noel -
What would be the point of having the trigger then??
Just call all the procs in logical order from the same place... Makes less hidden code and can save a few hours (at least minutes) wondering where the problem is coming from.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply