CODEWITHSUNDEEP
sqlsql-serverdatabasetriggers
Zizoo
Zizoo

Reputation: 1754

How to send email to multiple recipients when new row inserted ?

I'm using SQL Server 2014

I have a table A with these columns:

planNo, parcelNo, id

And another table B with these columns:

planNo, parcelNo, userEmail

When a new row is inserted into table A, I want to send an email to userEmail for all rows in table B that has the same parcelNo and planNo

Upvotes: 0

Views: 1132

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You know the answer, because you have tagged the question .

In my opinion, sending emails through triggers is dangerous. I much prefer an API based approach that uses stored procedures. Mistakes in triggers can cause the database to lock.

Instead of inserting a row directly into the table and having a database trigger do the email, call a stored procedure to insert a row in A and then send the email in the stored procedure. So, the stored procedure would look something like this:

create procedure usp_InsertAndEmail (
    @planNo ??,
    @parcelNo ??,
    @id
) as
begin
    declare @email varchar(255);

    insert into a(planNo, parcelNo, id)
        values (@planNo, @parcelNo, @id);

    declare cursor b_cursor for
        select email
        from table b
        where b.planNo = @planNo and b.parcelNo = @parcelNo;

    open cursor b_cursor;

    fetch next from b_cursor into @email;

    while (@@fetch_status = 0)
    begin
        -- do email send however you do it
        fetch next from b_cursor into @email;
    end;
    close b_cursor;
    deallocate b_cursor;
end;

This approach also allows you to check for errors on the emailing.

Upvotes: 2

Siegfried.V
Siegfried.V

Reputation: 1595

I guess you use PHP?

    $requete= 'SELECT * FROM B WHERE planNo=\'.$plannumber.\' AND parcelNo=\'.$parcelnumber.\'';
$result = $bdd->query($requete);
    if($result)
                {
                    while ($donnees = $result->fetch())
                    {
                        $email=$donnees['userEmail'];
                        $header='From: Name Surname <your_email>'."\r\n";
                        $title='title of your mail';
                        $message='your message';
                        mail($email, $title, $message, $header);
                    }
                }

Upvotes: 0

Related Questions