Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gobigred
Starting Member
2 Posts |
 Posted - 2007-12-31 : 14:49:06
|
| I have a SQL database that gets populated by another program. Is there a way to determine when a new record is added to that database?Thanks |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2007-12-31 : 14:55:41
|
| Do you mean you want to know when any existing row in a table was added? Or you want to be "notified" when a new row comes in?What are you trying to accomplish?Here's one common way to maintain a [createDate]:If the table had a datetime column like (createDate) with a DEFAULT of getdate(), then any row added would have it's create date. Of course with that type of implementation, anyone could explicitly update or insert whatever value they wanted, so you have to be carefull of how users access the data.Be One with the OptimizerTG |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-31 : 15:00:47
|
| Another approach will be to add a audit table which will monitor the changes in the main table. The audit table will be populated via a Trigger. For every action,the trigger will put values in the audit table along with action info (INSERT,UPDATE or DELETE). But as this method involves trigger, performance will drop. Go for this only if you cant touch the main table. |
|
|
|
gobigred
Starting Member
2 Posts |
Posted - 2007-12-31 : 15:12:39
|
quote:
Originally posted by TG
Do you mean you want to know when any existing row in a table was added? Or you want to be "notified" when a new row comes in?What are you trying to accomplish?Here's one common way to maintain a [createDate]:If the table had a datetime column like (createDate) with a DEFAULT of getdate(), then any row added would have it's create date. Of course with that type of implementation, anyone could explicitly update or insert whatever value they wanted, so you have to be carefull of how users access the data.Be One with the OptimizerTG
I want to be notified when a new row comes in or gets added. Once the new row comes in it will always trigger an event, which utilizes information added. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2007-12-31 : 17:54:25
|
| hmm, in that case how about this:create a new table like [processQueue]. Include the primary key of the other table as column(s) as well as a [processState] column. Create an AFTER INSERT trigger on the table you want to be notified about new rows. The trigger code should be very simple, just an insert to the processqueue table. There should be nothing to threaten the user transaction in this trigger.Then set up a sql job that is scheduled for every 5 minutes or every 5 seconds or whatever which will process the rows in the queue table that it hasn't processed on previous runs. The job can handle your processing as well as notifications. It will not be in the same process as the user transaction so that the user won't have delays or unexpected errors.Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Determine when a new record is added to a database