sql - How to fire a trigger after the SPROC has completed execution? -

sql - How to fire a trigger after the SPROC has completed execution? -

i have written trigger sends email 1 time row insert performed.

alter trigger tr_sendmailondatarequest on datarequest after insert begin set nocount on; declare @dr_id int, @dr_fullname varchar(200), @dr_email varchar(200), @dr_phone varchar(20), @ut_name varchar(50), @dr_usertypeother varchar(50) = null, @d_name varchar(200), @dr_requestdate datetime, @uf_linkedfiles varchar(max), @drn_names varchar(200), @dr_description varchar(1200), @dr_createdon datetime, @analystmaillist varchar(max), @tablehtml nvarchar(max), @downloadlink varchar(max) = n'none' select @dr_id = max(dr_id) dbo.datarequest select @dr_fullname = dr_fullname, @dr_email = dr_email, @dr_phone = dr_phone, @ut_name = ut_name, @dr_usertypeother = dr_usertypeother, @d_name = d_name, @dr_requestdate = dr_requestdate, @uf_linkedfiles = uf_linkedfiles, @drn_names = drn_names, @dr_description = dr_description, @dr_createdon = dr_createdon dbo.fn_getdatarequest(@dr_id) select @analystmaillist = dbo.fn_getanalystsmaillist() if (len(@uf_linkedfiles) > 0) begin set @downloadlink = n'<a href="http://localhost:8500/workrequest/index.cfm?event=downloads.index&id=' + cast(@dr_id varchar(10)) + n'&k='+ substring(master.dbo.fn_varbintohexstr(hashbytes('sha', ':be9[dcv9wf~w!?xx4jo0oxlbz@0p4+[~z0do|:u,of!13^xzb')), 3, 32) + n'">downloads</a>' end set @tablehtml = n'<h1>data request</h1>' + n'<ul>' + n'<li>full name: ' + @uf_linkedfiles + n'</li>' + n'<li>email: ' + @dr_email + n'</li>' + n'<li>phone: ' + cast(@dr_phone varchar(20)) + n'</li>' + n'<li>user type: ' + @ut_name + n'</li>' + n'<li>user type other: ' + coalesce(@dr_usertypeother, n'none') + n'</li>' + n'<li>reuest date: ' + convert(varchar(20), @dr_requestdate, 107) + n'</li>' + n'<li>downloads: ' + @downloadlink + n'</li>' + n'</ul>'; begin exec msdb.dbo.sp_send_dbmail @profile_name = 'example', @recipients = 'john doe<jdoe@example>', --@recipients = @analystmaillist, @reply_to = @dr_email, @subject = 'email test', @body_format = 'html', @body = @tablehtml end end go

the above trigger fired when there row insert operation on table datarequest. after row insert operation, take identity element generated after insert operation , utilize foreign key, , insert other values in different table. finally, utilize values both tables , create email sent.

i wasn't getting values other tables (e.g. @uf_linkedfiles), realized trigger beingness fired after insert in first table before insert in second table, no values available when sending email.

so how create sure trigger fired after sproc insert activities in multiple tables has completed transaction.

here table diagram -

instead of using trigger, have included email sending code in sproc rows beingness inserted.

sql sql-server triggers