July 20, 2010 at 7:47 am
Hi ,
i need to create a trigger that needs to be fired on a table when that table is idle for two hours.can anybody help me with the script.Awaiting for the reply.
July 20, 2010 at 9:02 am
Can you explain a bit what you mean by idle table?
Actually triggers are raised on some action (update, insert or delete).
If you want to raise something when nothing happens with table for some time period, it can not be a trigger.
You can base your solution on the following:
Create trigger for insert, update, delete and log the time when last action has happen into separate table (can be just single row and single column which will contain date and time of last action). Then you may create a scheduled job which will run every whatever number of seconds you want. This job will check the last action date and perform required task.
July 20, 2010 at 4:51 pm
palla.sureshbabu (7/20/2010)
Hi ,i need to create a trigger that needs to be fired on a table when that table is idle for two hours.can anybody help me with the script.Awaiting for the reply.
Can't be done by trigger. You need to create a scheduled job (scheduled to run every minute or so?) that samples the table for the last entry date and compares it against the current time.
--Jeff Moden
July 20, 2010 at 5:12 pm
Jeff is correct. Triggers only respond to actions, not inactions.
A job with a check is what I'd do.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 21, 2010 at 12:54 am
jeff,
can explain it bit clear how can we schedule the job please post in detail procedure with the script.
Once aging thanks for your reply.
July 21, 2010 at 3:44 am
To read about SQL Server Agent:
http://technet.microsoft.com/en-us/library/ms189237.aspx
As I said before, you will, most likely, need a trigger to log the action. Indeed, UPDATE and INSERT could be tracked just by having datetime column in the table itself, however want about tracking DELETE's
July 21, 2010 at 4:17 am
Eugene,
what my criteria is if the table is idle for two hours i,e no activity is done on the table for about two hours the trigger needs to fire and send the notification through the mail.
July 21, 2010 at 5:51 am
If you perform real DELETE on this table and you count it as activity you will need to do the following:
1. Create a trigger to log action and its timeinto separate table. For example:
You have a Table_A which you want to track activity on.
Create a table to hold the log, something like:
CREATE TABLE dbo.ActivityLog
(
TableName varchar(255)
,LastActionTime datetime
)
The trigger on your Table_A for UPDATE, INSERT, DELETE will need to "upsert" (update or insert) the log record with datetime, something like:
CREATE TRIGGER tg_TableA_LogActivity
ON Table_A
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.ActivityLog SET LastActionTime = GETDATE() WHERE TableName = 'TableA'
IF (@@ROWCOUNT = 0)
BEGIN
INSERT dbo.ActivityLog (TableName,LastActionTime) SELECT 'TableA', GETDATE()
END
END
You can also, log the action type (insert/update/delete), if you need it.
2. Create a job which will check ActivityLog and whatever required if the LastActionTime is two hours behind. Schedule to execute the job as often as you wish...
P.S. That's what I've already advised you in my first post...
July 21, 2010 at 6:43 am
palla.sureshbabu (7/20/2010)
Hi ,i need to create a trigger that needs to be fired on a table when that table is idle for two hours.can anybody help me with the script.Awaiting for the reply.
Just curious as to why you would need to know if a table is idle for more than 2 hours?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 21, 2010 at 12:00 pm
palla.sureshbabu (7/21/2010)
jeff,can explain it bit clear how can we schedule the job please post in detail procedure with the script.
Once aging thanks for your reply.
Don't you have a column in the table that contatins the date and time that a row was inserted?
--Jeff Moden
July 21, 2010 at 1:30 pm
Jeff Moden (7/21/2010)
...Don't you have a column in the table that contatins the date and time that a row was inserted?
It wouldn't help to track DELETE's...
July 22, 2010 at 4:01 pm
Eugene Elutin (7/21/2010)
Jeff Moden (7/21/2010)
...Don't you have a column in the table that contatins the date and time that a row was inserted?
It wouldn't help to track DELETE's...
No, but it would let you know when a row was more than 2 hours old so you could delete it.
--Jeff Moden
July 23, 2010 at 10:03 am
OP didn't actually state only modifications counted as actitity ...
Are SELECTs counted as "activity"?
If no UPDATEs/INSERTs/DELETEs are done on a table for 3 hours, but a SELECT is done every hour, do you consider the table idle or not?
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply