Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to create trigger that will update the record. The situation is like:

I have one column named CheckInDateTime and one more named Status. If the administrator is not able to update the Status within 30 minutes of CheckInDateTime I want to update the Status column of that particular row.

How can I update that particular row?

share|improve this question
    
Can you clarify what you mean by "if administrator not able to update the Status after half hour of checkintime. then I want to update the column of that perticular row" - it is unclear what you are attempting to do. –  Max Vernon Nov 14 '13 at 5:33
    
I will tell you exact what I'm want to do, If first time status field has value PendingForApproval. If admin is not able to approve that perticular record within 30 min, then i want to update that perticular record's value to be ForcefullyClosed. How can I do ? –  user1503152 Nov 14 '13 at 5:57
    
Which DBMS are you using? –  Max Vernon Nov 14 '13 at 5:58
    
MsSqlServer 2008 –  user1503152 Nov 14 '13 at 6:06
1  
You dont need a trigger for this. As Max pointed out, you have to use sql agent to schedule it. –  Kin Dec 14 '13 at 16:23

1 Answer 1

Create a task in SQL Server Agent that runs a stored procedure once every minute that updates the affected rows.

For instance, something like this might work:

CREATE PROCEDURE UpdateRows
AS
BEGIN
    DECLARE @Cutoff DATETIME;
    SET @Cutoff = DATEADD(mi, -30, GETDATE());
    UPDATE MyTable
    SET Status = 'ForcefullyClosed'
    WHERE CheckInDateTime < @Cutoff AND Status IS NULL;
END;
share|improve this answer
1  
Max Vernon thanks –  user1503152 Nov 14 '13 at 6:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.