get query result with trigger

Issue

This Content is from Stack Overflow. Question asked by Ilhan Ates

get query result with trigger

Hello, I want to get the code in the line when insert,
I will use the code I received in a query and create a table,
Then I will send this table to the interested parties by e-mail,

I created a trigger,
I got the code in insert,
My sorghum is ready,
My mail sending code is ready,

Problem: I can’t create table with sql query

    create trigger DilmeEmriMailGonder
  on [db].[dbo].[Table1]
  After insert as
  Begin
  DECLARE @DilmeEmri nvarchar(max);
  DECLARE @tableHTML nvarchar(max);
  DECLARE @Konu nvarchar(max);
 

select @DilmeEmri= s_code, @Konu=s_code+' - Dilme Emri Oluşturuldu' from inserted


set @tableHTML= 
N'<table border="1" cellspacing="0" cellpadding="0" width=95% style="border:solid #DFD7CA 1.0pt;" >'+
N'<tr style="height:22.7pt"><th>Dilme Emri</th><th>İş Emri</th><th>İşin Adı</th><th>Kağıt Kategori</th><th>Kağıt Alt Kategori</th><th>Kağıt Kodu</th><th>Kağıt Adı</th><th>Ölçü</th><th>Miktar</th><th>Makine</th>'+
CAST((
SELECT 
td =ctt.[s_code],'',
td =tq.s_orderID,'',
     ...
  FROM [db].[dbo].[Table1] ctt
  left join [db].[dbo].[Table2] cttd on ...
  left join [db].[dbo].[Table3] tp on ...
  left join [db].[dbo].[Table4] te on ...
  ....
  where ctt.s_code=@DilmeEmri and ctt.b_deleted=0
  for xml path('tr'), type )as nvarchar (max)) +
N'</table>'

exec msdb.dbo.sp_send_dbmail
@recipients = 'mail@mail.com',
@subject = @Konu,
@body=@tableHTML,
@body_format='HTML'
  End



Solution

This question is not yet answered, be the first one who answer using the comment. Later the confirmed answer will be published as the solution.

This Question and Answer are collected from stackoverflow and tested by JTuto community, is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?