Mail when value of field < or > paramater%
-
Hello,
I have a question and I don't know if it's possible to configure in SQL.
You have a ERP GUI screen, with SQL tables, and the user changed a value of a field.
In this printscreen the cost price. Then when the new value is < or > then 30% of the 'old' value a simple mail must be sent to a user. Let say ict@exchange.com (we use Exchange as mail server)
So an example : TO user XXXX, Subject text, body text is enough.
Or when the calculation of some fields < or > is then another field.
Example : cost price + transport + packing cost - discount is < or > 40% off the sales price.
As an example :
Table : Articles
Field : costprice, sales price, packing, transport and discount
For me it's importent how to start. How to configure stuff like this.
And then I can find/search on the internet for more solutions.
But now I have no clue how to start / configure ?
I'm a very beginner in SQL.
-
Wouldn't this logic be easier to implement into the application, rather than SQL Server? Otherwise you're going to need to (likely) rely on triggers, which shouldn't be used to send emails. You'll therefore need to store the details of the change in a table and then (regularly) query that data using agent and send the relevant email(s).
If you do this in the application you can hold the previous value in a variable, check the new one on save and then send the email if the difference is high enough. That's a lot cleaner (and easier) that the SQL solution.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Yes, you can implement it in the ERP system. But timing and cost price. Because change an ERP system cost a lot of money.
Some other guy post this text. But when I want to configure the mail then I get the message 'Not implemented'.
I'm scared to configure the mail. So I also read that you can save the original record to another file. But what is the code ?
************************************************************
You need to create trigger using CREATE TRIGGER statement
like
CREATE TRIGGER TriggerName
ON TableName
AFTER UPDATE
AS
BEGIN
IF EXISTS(
SELECT 1
FROM INSERTED i
JOIN DELETED d
ON d.PK = i.PK
WHERE ABS(d.costprice - i.cosetprice) > d.costprice * .01
)
EXEC sp_sent_dbmail ....
END
Here PK would be the primary key of the table
And sp_sent_dbmail is the procedure used for sending email notifications
You've to enable database mail feature for using this
see
https://www.sqlshack.com/configure-database-mail-sql-server/
-
I'm not a fan of triggers, so my initial thought would be to create a SQL Agent job which runs periodically (hourly? daily?), looking at all rows in the table where DateModified >= LastJobRunDate.
Any rows satisfying this would be checked against the validation rules & an e-mail sent out with details of all, or any, which do not satisfy them.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
And do you mean with 'checked against rules' manually or can you configure the validation ?
If yes how can you do that ?
Thx
-
I mean by running one or more queries which look for validation issues & passing the results of those queries to sp_send_dbmail.
There are many ways of doing it. Here is one:
Create a stored procedure
Create a temp table in the stored proc. This will contain any and all validation results. It can have as many or as few columns as you like, but I'd suggest something like (NameOfTest, RowDetails, TestResults).
Your proc runs the tests and inserts the results (ie, the rows which fail the tests) to the temp table.
At the end of the proc, if rows exist in the temp table, use sp_send_dbmail to send the contents of the temp table to the nominated recipients.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
I think the problem here is, Phil, that the Op doesn't have historical data stored (if they did this would be trivial). As you could easily just use
LAGto inspect the previous value of the row. So the OP needs a way of checking what the old and new values are. As a result they want to use a trigger to inspect the values in theinsertedanddeletedobjects to see if the value has changed enough to require an email to be sent.I agree, I'm not a fan of triggers, and simply checking the (current?) row's value against the last value would be far easier. if they don't have a history then i would suggest they use a trigger to create the history and then they can do as you say, and check against the last value.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thom, if that's the case, you are right.
But for the examples provided, I can't see how it's required (excepting, possibly, the change in cost price – though any half-decent application would track a history of changes as important as this).
For example, checking for rows where
(cost price + transport + packing cost - discount) >= (sales price * 1.4) or <= (sales price / 1.4 )
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
- Phil Parkin wrote:
Thom, if that's the case, you are right. But for the examples provided, I can't see how it's required (excepting, possibly, the change in cost price – though any half-decent application would track a history of changes as important as this). For example, checking for rows where (cost price + transport + packing cost - discount) >= (sales price * 1.4) or <= (sales price / 1.4 )
That's the thing, their one SQL query heavily implies they are checking old versus new prices:
SELECT 1
FROM INSERTED i
JOIN DELETED d ON d.PK = i.PK
WHERE ABS(d.costprice - i.cosetprice) > d.costprice * .01But you're right, if an application doesn't have price tracking, then that is an even bigger concern. For auditing perspectives you'd think that's a must (we certainly have to keep rating details for Insurance for years).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Hello,
Here I again.
Sorry for late respons but I've check something out.
I'm very new and have no experience. So, if possible, keep it simple 🙂
This is what I want but I don't now what the best method is for getting this result.
Reporting services, trigger, ...... ????
Note : Table 'Orders' is a table where it's not possible for the ERP developpers to check the value.
Description :
There is a SQL table 'Orders'. This table has a lot of fields.
When a new record, automatically, is created in the file then I want to test :
Has field "Type" the value 02 and is calculation : 1-((pur_price/qty_pur)/(val_pur/val_qty)) >0,10 of <-0,10 then a mail has to be sent to xxx@xxx.com
All the fields are in 1 record. So no check to other record.
Table structue :
Count | Type | Pur_price | Qty_pur | Val_pur | Val_qty | ......
125 02 15 10 52 5
Now a mail has to be sent.
I hope the picture is clear and that somebody can help me.
-
A trigger is actually perfect for this situation. For the email, it's probably best to add row(s) to a table or queue, then construct and send the emails based on what's in the table/queue, for example, by using a job to process them.
Something like this:
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER Orders__TR_INS
ON dbo.Orders
AFTER INSERT
AS
SET NOCOUNT ON;
INSERT INTO dbo.Orders_email (
PK, create_date, status
)
SELECT PK, GETDATE() AS create_date, 1 AS status /*1=emails needs sent/is pending*/
FROM dbo.Orders O
INNER JOIN inserted I ON I.PK = O.PK
WHERE (1-((pur_price * 1.0 / qty_pur) / (val_pur * 1.0 / val_qty))) BETWEEN 0.0 AND 10.0
/*naturally you'd need to create this job and related code to construct and send emails.*/
EXEC msdb.dbo.sp_start_job @job_name = 'Send_Orders_Email';
/*end of trigger code*/
GOSQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
Hello,
I've a few questions :
- How can I create a trigger ?
I go the SQL server. Databases - productiondatabase name - tables - dbo.Orders - on folder 'Trigger' right click - select 'New trigger'. But when I save the SQL code I get a pop-up to save the code in *.sql. So I have to give a name. When I do that I don't see the filename under folder 'Trigger'. I mean I thought that I see the filename here. What do I wrong ?
2. Users get a warning ?
I was busy with the trigger on the production data because I thought I can't do anything wrong but suddenly the users call me they get an error. They were working on the ERP system and enter orders. Do I need to take action or what do I have to do the users don't get this message ?
3. Sent a mail
On internet I wrote that you have to configure your database if you want to sent a mail. It's a complicated configuration.
But I also found this code.
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorks2012 Administrator',
@recipients = 'danw@Adventure-Works.com',
@body = 'Don''t forget to print a report for the sales force.',
@subject = 'Reminder';
GO
Is this also a correct code ?
4. Table dbo.orders_email
In your code I see, I think, table dbo.orders_email. I think I have to create this table manually with the fields PK, create_date and status. Or I'm wrong ?
5. Mail
If I have problems to sent the mail or there other options ? Put a file in a certain folder or ......?
And I want to thank you for the code.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply