Hello
The purpose of my trigger is :
When someone creates Order , an automatic email generate to inform certain person for the creation , with the details SOPnumber , Customer , shipping method and slaespersonne
The problems :
1 - When someone just click on SOPNUMBE , which generates a new SOPNUMBE it sends an email .2 - If two user typing an order at the same time , for example ( 23000 and 23001 ), one of both remove the order ( 23000 ) , if another user wants to enter a new order the sequence number does not take ( 23000 ), which is free, starts at ( 23002 ) . 3 - retrieve information ( SOPNUMBE , CSTPONBR ... ) in sending the e-mail are all empty except SOPNUMBE ,
can someone help me.
Create Trigger [dbo].[SOP_Order_New]
ON [dbo].[SOP10100]
After Insert
As
Begin
set nocount on;
DECLARE @SOPNUMBE AS char(21)
DECLARE @CSTPONBR AS char(21)
DECLARE @SHIPMTHD As char(15)
DECLARE @SLPRSNID As char(15)
DECLARE @Message AS Varchar(max)
DECLARE @Xml As varchar(max)
Set @SOPNUMBE = (select SOPNUMBE from inserted where SOPTYPE=2)
Set @CSTPONBR = (select CSTPONBR from inserted where SOPTYPE=2)
Set @SHIPMTHD = (select SHIPMTHD from inserted where SOPTYPE=2)
Set @SLPRSNID = (select SLPRSNID from inserted where SOPTYPE=2)
Set @Xml=
CAST(( select
@SOPNUMBE As 'td','',
@CSTPONBR As 'td','',
@SHIPMTHD As 'td','',
@SLPRSNID As 'td'
from inserted
For XML Path ('tr'),ELEMENTS ) AS nvarchar(Max))
if @SOPNUMBE <>''
begin
Set @Message='<html><body><H3>New Order Info</H3>
<table border = 1>
<tr>
<th>Document Number </th>
<th>Customer Number </th>
<th>Shipping Methode </th>
<th>Salesperson </th>
</tr>'
Set @Message= @Message + @Xml +'</Table></body></html>'
Exec msdb.dbo.sp_send_dbmail
@profile_name='Email Alerts',
@recipients='it@test.com',
@body=@Message,
@body_format='HTML',
@subject='New order has been created';
end
end
