Create a trigger based on time

Question

Create a trigger based on time

ramadesai108

SSChampion

Points: 10609
More actions
September 15, 2009 at 10:22 am

#135021

Hi All,
How do I create a trigger based on time. For example, I have a date/time column in my table. I want to compare the current time with the time in the table row and if the current time is 5 minutes ahead of the value in the date/time row then I want to update a value in another column in the same table.
Thanks.

Viewing 7 posts - 1 through 7 (of 7 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

ramadesai108 (9/15/2009)
Hi All,
How do I create a trigger based on time. For example, I have a date/time column in my table. I want to compare the current time with the time in the table row and if the current time is 5 minutes ahead of the value in the date/time row then I want to update a value in another column in the same table.
Thanks.

It is just a normal insert/update trigger, you would use the inserted table to get the value of the new/updated row you could then do your update.

You also need to keep in mind that the inserted table could contain more than one row so your logic needs to keep that in mind. Also if triggers are allowed to fire other triggers you need to be careful that you don't create a scenario where the triggers fire each other.

Is that helpful at all?

CEWII

ramadesai108 SSChampion Points: 10609 More actions · Answer 2

ramadesai108

SSChampion

Points: 10609

September 15, 2009 at 10:51 am

#1053965

Thanks for the reply, but I am not clear.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Your requirement sounds like you'd like to change some values based on a time "trigger".

This is not what a trigger is designed for.

A trigger on a table is activated after insert, update, and/or delete, but not at a specific time.

The comparison between current time and time of a column only gets triggered if the data in the table will be manipulated using one of the above DML keywords.

If my description is not what you're looking for you might add some more information to your request.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

steveb. SSC-Forever Points: 46733 More actions · Answer 4

It seems like you may want to schedule a job to run on the database rather than use a trigger, as mentioned triggers have to be fired by inserts/updates/deletes.

Either way, perhaps you could post a bit more detail about what you want to achieve?

Lowell SSC Guru Points: 323522 More actions · Answer 5

you get the best results here if you provide full details(if you can), the CREATE TABLE, the specific condition(THIS column changes, affect THIS OTHER column)

here's an example which *I Think* demonstrates your condition: if a row is updated, and some datetime column is different by more than 5 minutes, you update a column in the same row:

typical results:

WHATEVERID    DESCRIP    INSERTDT                   UPDATEDDT                  FIVEMINUTESPASSED
1             APPLES     2009-09-14 13:14:06.213    2009-09-14 13:14:06.213    NULL
2             BANANAS    2009-09-14 13:14:06.213    2009-09-14 13:14:06.213    NULL
3             CHERRIES   2009-09-14 13:14:06.213    2009-09-14 13:14:06.213    NULL
4             GRAPES*    2009-09-14 13:14:06.213    2009-09-15 13:14:14.307    CHANGED!
5             KIWI*      2009-09-14 13:14:06.213    2009-09-15 13:14:14.307    CHANGED!

CREATE TABLE WHATEVER(

WHATEVERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

DESCRIP VARCHAR(30),

INSERTDT DATETIME DEFAULT GETDATE(),

UPDATEDDT DATETIME DEFAULT GETDATE(),

FIVEMINUTESPASSED varchar(30),

)

INSERT INTO WHATEVER(DESCRIP,INSERTDT,UPDATEDDT)

SELECT 'APPLES' ,GETDATE() -1 ,GETDATE() -1 UNION

SELECT 'ORANGES' ,GETDATE() -1 ,GETDATE() -1 UNION

SELECT 'BANANAS' ,GETDATE() -1 ,GETDATE() -1 UNION

SELECT 'GRAPES' ,GETDATE() -1 ,GETDATE() -1 UNION

SELECT 'CHERRIES' ,GETDATE() -1 ,GETDATE() -1 UNION

SELECT 'KIWI' ,GETDATE() -1 ,GETDATE() -1

GO

CREATE TRIGGER TR_WHATEVER

ON WHATEVER

FOR INSERT,UPDATE

AS

BEGIN

--update a specific column on a specific condition

UPDATE WHATEVER

SET FIVEMINUTESPASSED = 'CHANGED!'

FROM INSERTED

WHERE WHATEVER.WHATEVERID = INSERTED.WHATEVERID

AND DATEDIFF(minute,WHATEVER.UPDATEDDT,GETDATE()) > 5

--update my column audit column like always

UPDATE WHATEVER

SET UPDATEDDT = GETDATE()

FROM INSERTED

WHERE WHATEVER.WHATEVERID = INSERTED.WHATEVERID

END --TRIGGER

GO

INSERT INTO WHATEVER(DESCRIP)

SELECT 'CANTALOUPE' UNION

SELECT 'TANGARINES' UNION

SELECT 'PLUMS' UNION

SELECT 'PEACHES' UNION

SELECT 'NECTARINES'

SELECT * FROM WHATEVER

UPDATE WHATEVER SET DESCRIP = DESCRIP + '*' WHERE WHATEVERID IN (4,5)

SELECT * FROM WHATEVER

DROP TABLE WHATEVER

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ramadesai108 SSChampion Points: 10609 More actions · Answer 6

Lutz and SteveB are correct. In this case I cannot create a trigger, but I need to schedule a job.

Thanks all for your time.

Create a trigger based on time

Cookies on SQLServerCentral