Mail notification

Transact-SQL
1

Ho team I have encounter with a requirement.
Here goes the scenario.

I have a table associates with columns
Empid(pk),name , designation, level,mail,extn no......etc

When ever if an employee assigned with a task he should receive the assignment thru mail.

2

One option would be to use trigger.

3

hi can u pls explain in details

4

another option is sql job

5

Ideally you have a stored procedure adding the assignment to the table - and if so you just need to modify the stored procedure to send an email when an assignment is added.

If you don't have a stored procedure performing the insert (why not?) then your options are:

  1. Service Broker
  2. Agent Job
  3. Trigger

I would not recommend using a trigger - to do that you need to record the information in a separate table that works like a queue and have a job process the queue. That is essentially the same as using an agent job - where the agent job is scheduled to run every xx minutes - pulling any new records - then using a cursor over the result set you call sp_send_dbmail.

With a service broker - SQL monitors the table and adds rows to the queue - then processes the queue. However, this requires a lot of time and effort to setup and build.

6

Hi Jeff.. Am using stored procedure to insert data into allocation table.

This has 3 parameters

  1. Emp name,( from emp table . it has email)
  2. Uid( manager enters run time)
  3. Stage.( from stage table)

When this procedure is inserted then a mail to be sent he mail I'd of the emp with uid and stage information.