TSQL

Question

Post reply

TSQL

Suresh Babu Palla

SSChasing Mays

Points: 610
More actions
July 20, 2010 at 7:47 am

#224263

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.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

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.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

Jeff is correct. Triggers only respond to actions, not inactions.

A job with a check is what I'd do.

Suresh Babu Palla SSChasing Mays Points: 610 More actions · Answer 4

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.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

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

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Suresh Babu Palla SSChasing Mays Points: 610 More actions · Answer 6

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.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

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

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

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Mike01 SSChampion Points: 11395 More actions · Answer 8

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/

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

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

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 12

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