Insert Notification

Question

Post reply

Insert Notification

tt-615680

SSCertifiable

Points: 7835
More actions
September 29, 2008 at 4:31 am

#193755

I am trying to write a script that would notify me if a table has inserted 400 rows, I am not sure if it is an insert trigger or not. Thank you in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

tt (9/29/2008)

I am trying to write a script that would notify me if a table has inserted 400 rows, I am not sure if it is an insert trigger or not. Thank you in advance.

Can you be more specific in what you need? Do you need to know when the table has 400 records? Do you need to know when 400 records were inserted into the table in one insert statement? Do you need to know when it is exactly 400 or more then 400? Do you need to know it as soon as it happens or can you wait before the notification?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

tt-615680 SSCertifiable Points: 7835 More actions · Answer 2

tt-615680

SSCertifiable

Points: 7835

September 29, 2008 at 4:47 am

#878320

I just want to be notified when the table has 400 rows inserted.

Thank you.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

tt (9/29/2008)

I just want to be notified when the table has 400 rows inserted.

Thank you.

Any reason that you refuse to answer the questions that asked you? In any case here are some ways to be notified when a table has 400 records:

One way is to create a trigger for insert and from the trigger check how many records are in the table and then decide if you should be getting the notification or not. Notice that if you do it from within a trigger that the notification process will fail, then the whole insert might fail. Another problem is that you’ll count the number of records after every insert statement.

Another way is to do the insert from a stored procedure and not give anyone permission to insert data directly into the table. In the procedure you can check how many records are in the table and then decide if you want to send the notification or not. In this scenario you can make sure that the insert statement will run even if the notification process failed. The down side of this way is that administrators will be able to insert data directly into the table and then the stored procedure will not be invoked.

The third way is to create a job that checks the table every few minutes (the interval is up to you). This way the insert statement and the notification process are completely disconnected. It might ease the pressure on the table (depends on the amount of inserts and the interval that you’ll select) but few minutes might pass between the time that the table reached the 400 records and the time that you got the notification.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/