Sql Server Database stored procedure triggered when a value becomes 0

Question

I have a column in one of my tables that acts as a countdown. So as the current datetime approaches the endtime, it works its way down to 0. The countdown's value becomes 0 once the endtime is reached. When this happens, I would like it to trigger a stored procedure. Does anyone know how I could create a trigger such as this?

Table:

CREATE TABLE t1 (
id int IDENTITY,
enddate datetime NOT NULL,
daysleft AS (DATEDIFF(dd, GETDATE(), enddate))
);

INSERT INTO t1 (enddate)
VALUES(DATEADD(dd, 1, GETDATE())),
(DATEADD(dd, 2, GETDATE())),
(DATEADD(dd, 15, GETDATE()))

I haven't created the procedure yet, only because it would be useless if I can't create my required trigger.

Just added the table if that helps – Vigs Jul 19 '15 at 01:45 — Vigs, Jul 19 '15 at 01:45

score 0 · Accepted Answer · edited May 23 '17 at 10:28

I don't think there is a construct in SQL Server that allows you to run a stored procedure when the value of data equals user defined value. The reason behind this is if something as such were to be made, the performance of SQL Server would be tremendously impacted.

I would instead recommend you to consider writing a Windows Service that periodically wakes up and checks whether there are any rows in your table that have expired and then invoke your intended stored procedure on them. I can give you a sample of how to achieve this if you would like.

EDIT: A sample implementation of Windows Service

Okay so as I said above, there is no way of being notified when a particular data has reached its expiry time. So instead of being in the push notification model, we are working in a pull notification model where we periodically query the database for expired rows (e.g. something like "SELECT id FROM t1 WHERE enddate = @todayDate")

Now the key part of this solution is we need a periodic service. This has been demonstrated in the another user's answer here. The method where we will do our SQL operations is private void timer_Elapsed(...).

private void timer_Elapsed(object sender, System.Timer.ElapsedEventArgs e)
{
    // since I am not sure whether you are accessing your database using ADO.NET 
    // (i.e. SqlConnection, etc.) or Entity Framework (i.e. DbContext), 
    // I will assume ADO.NET from here on

    // retrieve all rows that have expired
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();

        var command = new SqlCommand("SELECT id FROM t1 WHERE enddate = @todayDate", connection);
        var paramDate = new SqlParameter("@todayDate", DateTime.Now.Date);
        command.Parameters.Add(paramDate);
        var reader = command.ExecuteReader();

        while (reader.Read()) 
        {
            var storedProcCommand = new SqlCommand("EXEC CleanUpExpiredRow @id", connection);
            var paramId = new SqlParameter("@id", reader.GetInt32(0));
            storedProcCommand.Parameters.Add(paramId);
            command.ExecuteNonQuery();
        }
    }
}

NOTE None of this code is tested.

To add to Parth's note, SQL Server triggers respond to database events and not conditional expressions, specifically: DDL events --CREATE TABLE, DROP TABLE, ALTER TABLE and DML events --INSERT, UPDATE, DELETE. Another suggestion includes running this insert query when `daysleft = 0` at the client/application level. This ties into a [current debate on the use of stored procedures](http://programmers.stackexchange.com/questions/65742/stored-procedures-a-bad-practice-at-one-of-worlds-largest-it-software-consulting) on how business logic should be abstracted from database processes. — Parfait, Jul 19 '15 at 02:43
@Parfait I agree with the debate however I have walked down the road of EntityFramework and have my hands burned pretty bad when it came to enforcing data security (i.e. only those who have the sufficient access to data should be able to read/write/delete that data). In my case, the user authentication mechanism was Forms, not the built in SQL Server ones. I have yet to find a way of enforcing data security other than stored procedures that can allow me to safely say I am not worried of sensitive data being accessed by wrong people. — Parth Shah, Jul 19 '15 at 02:55

Sql Server Database stored procedure triggered when a value becomes 0

1 Answers1