Email Alert on Status change 1

[Cpreston]

Hi

I have created a view that selects data based on a sales order that is still being produced but as a Journey number.

The status of the works order is Waiting for Processing (number. I want to be able to be alerted when this works order changes from Waiting for processing (Status 4) to Processed (Status 5).

I have created a SP that runs the exis8ing view and emails me to say we have a Waiting for processing works order with a Journey Number.

But cannot figure out how to send an alert when the status as changed from Waiting for processing to Processed. Basically the View only looks at Status 4 with a Journey Number, so when the the works order changes to Status 5, it no longer exists in the View.

Any ideas with the logic or how to achieve the final goal

Thanks

 

[bborissov]

Create a trigger and use sp_send_dbmail SP to send mail when status changes from 4 to 5.

Borislav Borissov
VFP9 SP2, SQL Server

 

[Cpreston]

Hi

Yes was thinking the Trigger route, not created one before so trying to learn myself from some Google examples.

However, if the View I have only shows one record, and the view only shows status 4, how do I know it as change to Status 5, as it dos not show in the View anymore.

The View is based on 4 tables OrderHeader, Worksorderheader, orderline and product. The status would change on the worksorderheader.

I need some way of identifying the information in the view that was there and now as changed so not in the view anymore. oep that makes sense.

Thanks

 

[bborissov]

You must have create trigger on the table where you keep Status,
Not on the View.

Borislav Borissov
VFP9 SP2, SQL Server

 

[Cpreston]

Hi

Yes but the View to get the filters require a view so I can get the information.

The Status is in the table Worksorderheader which is part of the view. So there lays the problem.

 

[Cpreston]

In fact I am struggling with the code.

The ON Testtable2 does not recognise the table even though it exisit. Also what code needs to go after FOR UPdate, so it checks if the status code as change on the table Testtable2.WOstatus. Any help please.

Thanks

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER WOstatuschange

ON Testtable2

FOR UPDATE



AS 
  EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'serverSQL',
	@recipients = 'name@compnay.co.uk',
	@subject = 'Change of Order Status Alert ',
	@query = 'Select * From [Databsename].[dbo].[TestTable2]',
	@attach_query_result_as_file = 0 ;
GO

 

[bborissov]

USE YourDataBase
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER dbo.WOstatuschange ON dbo.Testtable2
AFTER UPDATE
AS 
BEGIN
 IF UPDATE("Status")
    BEGIN
	  EXEC msdb.dbo.sp_send_dbmail
		@profile_name = 'serverSQL',
		@recipients = 'name@compnay.co.uk',
		@subject = 'Change of Order Status Alert ',
		@query = 'Select * From [Databsename].[dbo].[TestTable2]',
		@attach_query_result_as_file = 0 ;
	END
END

Borislav Borissov
VFP9 SP2, SQL Server

 

[Cpreston]

Hi

Ran into a problem, since adding the Trigger, I cannot run anything against testtable2.

If I run a simple select * from test table2 the query just sticks on execute. If I right click on th table and try and select top 100 roiws it does the same.
If I try and drop the trigger the same and also try and drop the table the same.

SO cannot do anything at all at the moment. Any ideas how I can free up whatever is causing the execution of anything on the table to free, so I can run things on it at normal speed.

Querying all other tables is fine and works as normal.

Thanks

 

[bborissov]

eMailing is a slow job,
why not create some SQL Job that will check if there is some new status changes and if there is any then the job should send mail?
you can schedule it to run on some acceptable interval.

Borislav Borissov
VFP9 SP2, SQL Server

 

[Cpreston]

Hi

We have other emailing procedures and they all work fairly quick.

I need to drop the Trigger and the Table, but wont let me do a thing to it.

I was thinking about this last night and ideally if I could get an email alert working on one table, Oderheader.

What we are looking for on the orderheader would be

Status = 4 and JouneyID not null (this means a Journey as been prepared before good are ready)

If Status is then change to 5 then alert require with Ordernumebr and JourneyNumber to be emailed. (this means the journey report need opriting again so product pack codes can be picked)

So how could this be achiever and what would the programming be like (sorry I know SQL a little but not hot on the programming side)

Firstly though I need to get rid of the trigger and table I have crested toe alst attempt.

Thanks for the replies

 

[Cpreston]

Hi

managed to clear the problem with trigger and table.

So any ideas how to achieve the issue of reporting on the changes.

Thanks

 

[Cpreston]

Hi

Ok the query below gives the result. So I need something that then alerts when the 3 changes to a 4, Still thinks a tiregger may be the way, but how do I program this. I am using our tests system so cannot break anything. Any ideas would be welcome, thanks

SELECT     OrderNumber, JourneyID, OrderStatus
FROM         dbo.OrderHeader
WHERE     (NOT (JourneyID IS NULL)) AND (OrderStatus = 3)

 

[Cpreston]

On second tho0ught perhaps a stored procedure that runs every 30 minutes would work better. I have googled a lot on it and a lot of people advise against using emails with Triggers.

So assuming a Stored procedures would work, could someone advise on the required, struggling to find some examples on line.

Thanks

 

[bborissov]

When I need to send mails when some of the data is changed then I have one more table or field(depending of what I have to watch) where I store the last values before I send the mail.
Then:
1. Check what values are changed and which records I have to send.
2. Send mail
3. Update these values in the table(field)
Something like:
(when you have one field)

 EXEC msdb.dbo.sp_send_dbmail
		@profile_name = 'serverSQL',
		@recipients = 'name@compnay.co.uk',
		@subject = 'Change of Order Status Alert ',
		@query = 'Select * From [Databsename].[dbo].[TestTable2] WHERE OldStatus <> Status',
		@attach_query_result_as_file = 0 ;
UPDATE Databsename].[dbo].[TestTable2] SET OldStatus = Status WHERE OldStatus <> Status

When you use table:

 EXEC msdb.dbo.sp_send_dbmail
		@profile_name = 'serverSQL',
		@recipients = 'name@compnay.co.uk',
		@subject = 'Change of Order Status Alert ',
		@query = 'Select * From [Databsename].[dbo].[TestTable2] TestTable2
                              INNER JOIN [Databsename].[dbo].[TableWithOldValues] TableWithOldValues ON TestTable2.PK = TableWithOldValues.PK
                                                                                                     AND (TestTable2.Fld1  <> TableWithOldValues.Fld1
                                                                                                        OR TestTable2.Fld2 <> TableWithOldValues.Fld2
                                                                                                        ...
                                                                                                        OR TestTable2.FldN <> TableWithOldValues.FldN)',
		@attach_query_result_as_file = 0 ;
UPDATE TableWithOldValues SET Fld1 = TestTable2.Fld1
                            , Fld2 = TestTable2.Fld2
                            ...
                            , FldN = TestTable2.FldN
FROM [Databsename].[dbo].[TableWithOldValues] TableWithOldValues 
INNER JOIN [Databsename].[dbo].[TestTable2] TestTable2  ON TestTable2.PK = TableWithOldValues.PK
                                                      AND (TestTable2.Fld1  <> TableWithOldValues.Fld1
                                                        OR TestTable2.Fld2 <> TableWithOldValues.Fld2
                                                        ...
                                                        OR TestTable2.FldN <> TableWithOldValues.FldN)

Both codes are NOT TESTED!!!!

This is the simple code of my SP and I have a job which is executed every day (usually my users don't want to be updated very often ).




Borislav Borissov
VFP9 SP2, SQL Server