Professional Web Applications Themes

Triggering stored proc after number of rows or time expiry? - Microsoft SQL / MS SQL Server

Hi, Is there a way to trigger a stored procedure when one of these two conditions are met (whichever comes first): after a certain number of rows have been inserted in a table since the last time it ran after a certain (fixed) time period since the last time it ran. e.g. every 1000 rows or every 5 minutes, depending on what comes first. I'm thinking of using a trigger on the table to increase a count value on a row in another table, and a trigger on that table to execute the stored proc when the count reaches a ...

  1. #1

    Default Triggering stored proc after number of rows or time expiry?

    Hi,

    Is there a way to trigger a stored procedure when one of these two
    conditions are met (whichever comes first):
    after a certain number of rows have been inserted in a table since the
    last time it ran
    after a certain (fixed) time period since the last time it ran.

    e.g. every 1000 rows or every 5 minutes, depending on what comes first.

    I'm thinking of using a trigger on the table to increase a count value on a
    row in another table, and a trigger on that table to execute the stored proc
    when the count reaches a certain value. But how do i implement the second
    condition above? Using a maintenance job wont do the trick, as it doesn't
    care if the stored proc was executed just seconds before it was due to be
    triggered, it will just run it again.

    Thanks
    Alex

    --



    Alex M Guest

  2. #2

    Default Re: Triggering stored proc after number of rows or time expiry?

    Not in my case, perhaps I should have been more specific. Rows will only be
    added to the table, never updated or deleted. I use an identity column as a
    primary key, so as long as the row id of the source table does not exist in
    the "processed" table, the row is new. Doing it this way allows us to
    "force" the proc to re-run on old rows by just deleting the entries from the
    "processed" table.

    We're still looking at other ways to sort out the second condition of the
    rules somehow.

    Alex

    "Anith Sen" <anithbizdatasolutions.com> wrote in message
    news:eK3s7gWRDHA.2480tk2msftngp13.phx.gbl...
    > Regarding every 1000 rows, there should be much more information required
    to
    > come up with a solution, like which rows should be considered, what about
    > deletes & updates, what is the key column etc. As far as the time based
    > triggering, if you want to trigger a procedure every 5 minutes, the best
    way
    > is to create a job & schedule it to run every 5 minutes.
    >
    > --
    > - Anith
    > ( Please reply to newsgroups only )
    >
    >

    Alex M Guest

Similar Threads

  1. cfc and stored proc
    By mcoop in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: April 13th, 05:09 PM
  2. ASP vs Stored Proc vs UDF
    By Brad in forum ASP Database
    Replies: 11
    Last Post: October 28th, 01:46 AM
  3. Frequent session time Expiry
    By Thotatri in forum ASP.NET General
    Replies: 0
    Last Post: July 21st, 12:27 PM
  4. Stored proc problem on 9.30.UC1
    By Weaver, Bill in forum Informix
    Replies: 1
    Last Post: July 9th, 02:35 PM
  5. stored proc and tcp/ip
    By Helmut Wöss in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: June 30th, 08:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139