Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


Quick sqlserver help needed... Please
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

Quick sqlserver help needed... Please

I have a weird scenario.. Sqlserver db. Table has a column called status and record inserted date time column named datets.

Initial value of status column is "started". I want to automatically update this value to either "error" or "completed" , randomly between 20 and 40 minutes after the row is inserted.

How can I write a trigger or stored procedure in sqlserver to achieve this?

This is simply needed to simulate some test conditions...

Thanks.

Comments

  • Is it a mysql database or MSSQL DB ?

  • WeblogicsWeblogics Member
    edited November 2016

    Assuming this is MS SQL Server

    There are a number of ways to insert 2 values and it depends what works best for you.

    Google "sql server random insert values into table"

    As for how to trigger. If you know the exact initial start time, setup a SQL job to start the update SP.

    Or you can use the WAITFOR transact Sql method in your SP.

    WAITFOR Delay 00:20.

    You'll need to add single quotes before and after the 00:20 (Cloudfare craps the bed if I try to add the quotes here.)

    Let me know if that helps. I am not a certified SQL Server db admin but work extensively with SQL Server in my current day job for almost 20 years.

  • It would be helpful to know what language you are using in your application code (which is the place I would put this logic, as opposed to in the DB itself.

    If you are using node.js, I would use setTimeout( with 20 to 40 mins as the time).
    If java, look into awt.Timer (same idea as above).
    etc, etc.

    Then before runing the sql statement, just flip a digial "coin".

    Most languages have a Math.random() function or equivalent. In javascript it would look like this:

    var coin = Math.floor(Math.random()*2);
    if (coin==1) {
    insert("error")
    } else {
    insert("completed")
    }

  • Really speaking this is a DB and you should try as much as possible (for performance reasons) to update many rows at once instead of the row based trigger/procedure approach.

    Assuming that what I said makes sense and is applicable/relevant, just run a scheduled batch job every 20mins to sleep for a random time between 0-20mins and just update all the relevant rows (where status="started") with a random value ("completed" or "error"). This will be fast and performant but may not satisfy the individual rows get updated at different times constrait.

    See: stackoverflow.com/questions/5003028/how-can-i-fill-a-column-with-random-numbers-in-sql-i-get-the-same-value-in-ever for details on how to get a random value per row at insert time.

    If you're really insistent on doing this per row more frequently use a cursor and write a for loop to do what I just said per row or per-smaller-set of rows.

    Having a trigger "sleep" for 20mins is NOT a good idea.

    HTH.

  • nullnothere said: Having a trigger "sleep" for 20mins is NOT a good idea.

    True, but there is a difference from a sleep than a timer. A timer (in general) adds an event to a queue that is executed after a period of time.

    On the other hand, "sleep" for 20mins often puts the executing thread to sleep. This would tie up a the thread for the entire sleep time.

    Doing a timer in Java, node, etc is very easy, but I am not quite sure how it would work in php.

  • I ended up writing a client app to do the needful. Guess I was trying to chew more than I could bite.

    Thanks for the input

  • raindog308raindog308 Administrator, Veteran

    AdamM said: It would be helpful to know what language you are using in your application code (which is the place I would put this logic, as opposed to in the DB itself.

    OP wants to update a column on a regular basis without external input. If that isn't the definition of an ideal database job, I don't know what is.

  • raindog308 said: OP wants to update a column on a regular basis without external input. If that isn't the definition of an ideal database job, I don't know what is.

    I did not read that the OP wanted it to be an operation solely in the DB, with no application code. I guess there is more than one way to cook a steak. When all things are equal, I like to keep my database pretty ignorant of any logic. I don't like to put if/than type statements/loops/conditionals/triggers/timers/etc into the database itself. This is likely due to the fact that I am primarily an application programmer and only a DBA by necessity.

    Having an ignorant database, seems to make my SQL more portable (as different database see, to have their own ways of doing timers, conditionals, etc). And it keeps the database less active (which is good if the database is a shared resource).

Sign In or Register to comment.