quote:

---

Originally posted by barnabeck

After defining a trigger that reacts on the insertion of a new record, I can not launch the insert comand as I used to do through a web-application that uses the asp:sqldatasource module.

As soon as I remove the trigger, everything works the way it used to. Obviously there is a conflict between the asp insert command and the trigger itself. The trigger works smoothly if I manually insert the record.

Anyone a clue of how to avoid this?
Regards, Martin

---

ALTER TRIGGER [dbo].[TicketMailer] 

ON [dbo].[tickets] 

AFTER INSERT


AS BEGIN

IF EXISTS (SELECT top 1 correo FROM tickets order by CREATEDDATE desc) 

BEGIN

DECLARE @contenido varchar(4000),@str varchar(4000)

SELECT top 1 @str = correo, @contenido = 'Hola ' + remitente + CHAR(13) + CHAR(13) +'Confirmamos de ...' + CHAR(13)+ 'Descripción: '+ descripcion FROM tickets order by CREATEDDATE DESC;

EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Help_Desk',
        @recipients =  @str,
        @blind_copy_recipients = 'xxxx@xxx.xx',
        @body = @contenido,
        @subject = 'Help Desk';
END

END

<asp:SqlDataSource runat="server" id="SqlDataSource1" ProviderName="System.Data.SqlClient" ConnectionString="Data Source=XXXXXX;Initial Catalog=XX;User ID=XXXXX;Password=XXXXX" 
InsertCommand="INSERT INTO dbo.tickets ([titulo],[descripcion],[number],[remitente], [autor], [correo]) 
Values (@titulo, @descripcion, (select top 1 case when (number = NULL OR number ='999') THEN '1' ELSE number+1 END from tickets order by createddate desc), @remitente, @autor, 
(select email from plantilla where plantilla.name = @remitente))"


<asp:FormView runat="server" id="FormView1" DefaultMode="Insert" DataSourceID="SqlDataSource1" AllowPaging="false">
		
<InsertItemTemplate>
	<table>
	<tr><td>Titulo:</td><td>
		<asp:TextBox Text='<%# Bind("Titulo") %>' runat="server" id="TituloTextBox" />
	</td></tr><tr><td>
		Descripción:</td><td>
 	           <asp:TextBox Text='<%# Bind("descripcion") %>' wrap="true" TextMode="MultiLine" runat="server" id="TextBox_Descripcion" style="overflow:auto;" />	
	</td></tr><tr><td>
		Autor: </td><td>
            	<asp:RadioButtonList id=RadioButtonAutor runat="server" RepeatDirection="Horizontal" SelectedValue='<%# Bind("autor") %>' >
                   	<asp:ListItem>Óscar</asp:ListItem>
                  	<asp:ListItem>Martin</asp:ListItem>
                  	<asp:ListItem selected>IT</asp:ListItem>
               	</asp:RadioButtonList>
	</td></tr><tr><td>
		Remitente:</td><td>
		<asp:SqlDataSource runat="server" id="ADLookUp" ProviderName="System.Data.SqlClient" ConnectionString="Data Source=XXXXX;Initial Catalog=IT;User ID=XXXX;Password=XXXXX"
		SelectCommand="SELECT DISTINCT(CASE WHEN name IS NULL THEN ' select' ELSE name END) AS who FROM plantilla FULL OUTER JOIN wildcard ON plantilla.name = wildcard.wildcard">
		</asp:SqlDataSource>				
		<asp:DropDownList SelectedValue='<%# Bind("remitente") %>' runat="server" id="remitenteDropdown" DataValueField="who" DataTextField="who" DataSourceID="ADLookUp" >
		</asp:DropDownList>
	</td></tr>
	</table>
	<asp:LinkButton runat="server" Text="Saca el Ticket" CommandName="Insert"  id="InsertButton" CausesValidation="false" />	
	</InsertItemTemplate>	
</asp:FormView>

quote:

---

Originally posted by barnabeck

There must be something else that I'm missing. I did as you suggested and created a trigger that launches the job that then sends the mail. It didn't change anything.

It seems that as soon as I use the command 'exec' inside the trigger or the job, it interfers in some way with the insert command of the asp.net page. If I place a regular expression ('update of a column') inside of the trigger, everything works fine.
If I set up the trigger to launch a simple job ('update of a column') it doesn't work either. So it's not the execution of that specific sp_send_dbmail, but the EXEC in general that causes trouble.

Stuck

---