CODEWITHSUNDEEP
triggerssql-server-2008-r2
diyoda_
diyoda_

Reputation: 5420

How to ignore a trigger when there is still a trigger running

Problem:

I want to write a trigger when a table is inserted, I have to do a check and send an Email to a particular user. For this I am thinking of using a trigger that will insert some values to another QUEUE table. Then the trigger in the QUEUE table will get top x number of emails and send them and loop until the all records are done. So once the trigger is called, there can be insertions to the QUEUE table while the trigger is on work. So the trigger will wait and every time inserted to the QUEUE table and after the current running trigger is done.

Question:

Is my approach is correct. If there is a better method can some one help me. Is it a design issue if the trigger is fired over and over again. Is there a way to ignore the triggers while there is a running trigger.

Upvotes: 0

Views: 306

Answers (1)

AnandPhadke
AnandPhadke

Reputation: 13486

What I can suggest here is,

1.Create a trigger on main table as whenver new record is inserted into main table,insert the record into another table(QUEUE table).

2.Now create a sproc which will select top X rows from queue table and send emails.

3.Now create a sql job which will execute the above created sproc periodically.

4.In sproc you can handle like after sending email you can either delete those records from QUEUE table or do something with them.

Upvotes: 1

Related Questions