I have some SQL that works partially, I am looking for a change in a field (OrderStatus) and sending an email when the status changes from "S" to anything else AND the OrderType ='O'. My problem is the program front end sets the 'OrderStatus' to zero when an order is maintained so the trigger fires. I only want the trigger to fire on a genuine status change. Is this possible?
select @nvchOldStatus = OrderStatus from deleted
select @nvchNewStatus = OrderStatus from inserted
select @nvchDocType = DocumentType from inserted
-- If there has been a change in the status field
if @nvchNewStatus <> @nvchOldStatus and @nvchDocType ='O' and @nvchNewStatus <> '0'
select @nvchOrdNum = SalesOrder from inserted
select @nvchCust = Customer from inserted
select @nvchUser = LastOperator from inserted
SET @nvchText = 'Order Number: ' + @nvchOrdNum + ' For Customer: ' + @nvchCust + ' Released from Suspense by: ' + @nvchUser
if @nvchOldStatus ='S'
-- send your email