November 5, 2007 at 7:31 am
Hi all, I should start by saying that I'm a total newb at TSQL. I kinda inherited the SQL server administration and now I'm trying to write triggers for the first time, with next to no programming experience :crazy:
Hopefully you gurus can give me a hand with this one, it's turning out to be a little more complicated than I thought!
In a nutshell - when enters a sales order in our business app. Each line item on the order requires a commission cost. If the ordertaker forgets to enter the commission cost I want them to receive an email alert letting them know.
I know there are things I could do to make the trigger less resource intensive but this trigger will be rarely fired, maybe once or twice a week at most so I'm not too concerned about that. My goal is to keep it simple and functional.
I took another email alert trigger and started hacking it apart, this is what I have so far and if you guys see any glaring mistakes please let me know!!
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE TRIGGER [Zero_Commission_Cost_Notification] ON [dbo].[oe_hdr_salesrep]
WITH EXECUTE AS SELF
FOR INSERT, UPDATE
AS
DECLARE
@OrderNo as varchar(8),
@CustomerID as varchar(19),
@TakerID as varchar(15),
@TakerEmail as varchar(40),
@Recipients as varchar(50),
@Subject as varchar(80),
@BillToName as varchar(80),
@Commission as varchar(80)
SELECT @OrderNo = 'order_no',
@CustomerID = 'customer_id'
FROM inserted
SELECT @TakerID = 'taker'
FROM oe_hdr
WHERE order_no = @OrderNo
SELECT @TakerEmail = 'email_address'
FROM users
WHERE id = @TakerID
SELECT @BillToName = 'bill2_name'
FROM invoice_hdr
WHERE customer_id = @CustomerID
SELECT @Commission = min(select commission_cost
FROM oe_line
WHERE order_no = @OrderNo)
SELECT @Subject = 'Order #' + @OrderNo + ' for ' + @BillToName + ' has a $0 commission cost, please correct'
IF @Commission < .01
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Alerts',
@Recipients = @TakerEmail,
@Subject = @Subject
END[font="Courier New"][/font]
November 5, 2007 at 7:41 am
I just tried executing the script on a dev dbase and received this error:
Msg 156, Level 15, State 1, Procedure Zero_Commission_Cost_Notification, Line 28
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Procedure Zero_Commission_Cost_Notification, Line 30
Incorrect syntax near ')'.
November 5, 2007 at 7:46 am
SELECT @Commission = min(select commission_cost)
FROM oe_line
WHERE order_no = @OrderNo
N 56°04'39.16"
E 12°55'05.25"
November 5, 2007 at 7:51 am
^ thanks for catching that!
That fixed one error but I'm still getting the:
Msg 156, Level 15, State 1, Procedure Zero_Commission_Cost_Notification, Line 28
Incorrect syntax near the keyword 'select'.
For the SELECT statement right above, any thoughts?
November 5, 2007 at 7:57 am
this code with the minor changes required passes syntax for SQL2005: i think the issue was there was select min(select somevalue) instead of select min(somevalue)
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE TRIGGER [Zero_Commission_Cost_Notification] ON [dbo].[oe_hdr_salesrep]
WITH EXECUTE AS SELF
FOR INSERT, UPDATE
AS
DECLARE
@OrderNo as varchar(8),
@CustomerID as varchar(19),
@TakerID as varchar(15),
@TakerEmail as varchar(40),
@Recipients as varchar(50),
@Subject as varchar(80),
@BillToName as varchar(80),
@Commission as varchar(80)
SELECT @OrderNo = 'order_no',
@CustomerID = 'customer_id'
FROM inserted
SELECT @TakerID = 'taker'
FROM oe_hdr
WHERE order_no = @OrderNo
SELECT @TakerEmail = 'email_address'
FROM users
WHERE id = @TakerID
SELECT @BillToName = 'bill2_name'
FROM invoice_hdr
WHERE customer_id = @CustomerID
SELECT @Commission = min(commission_cost)
FROM oe_line
WHERE order_no = @OrderNo
SELECT @Subject = 'Order #' + @OrderNo + ' for ' + @BillToName + ' has a $0 commission cost, please correct'
IF @Commission < .01
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Alerts',
@Recipients = @TakerEmail,
@Subject = @Subject
END
Lowell
November 5, 2007 at 7:57 am
SELECT @Commission = min(commission_cost)
FROM oe_line
WHERE order_no = @OrderNo
EDIT:
Lowell has already pointed out above...., he is fast on keyboard:D
--Ramesh
November 5, 2007 at 8:24 am
You guys are all fast! It's only been about 30 minutes and you've helped me fix the problem with creating the trigger!
So, the trigger did execute and was created without errors - but it's still not doing what it was intended. I created a test sales order with a $0 comm cost and never received my alert email.
I ran a query to pull the order info and it does in fact show a $0 commission_cost
[font="Courier New"]commission_cost
---------------
0.000000000 [/font]
:unsure:
Do you think it has to do with the decimal placement?
The trigger says [font="Courier New"]IF @Comission < .01 [/font]
but maybe it should be 0.000000001 ?
Your thoughts?
November 5, 2007 at 9:00 am
ok here's my idea:
there's a statement that gets this value:
SELECT @Commission = min(commission_cost)
FROM oe_line
WHERE order_no = @OrderNo
i'm thinking that the lines here get inserted into the table oe_line AFTER the header record gets inserted in oe_hdr...since there's nothing found in that table the @TakerId is null, and nothing is found in the rest of the trigger; that is what prevents the header trigger.
does that sound right? maybe you need to move this logic to be performed after all teh data is inserted, and not when the header record is inserted?
also, the variable population is grabbing literal strings instead of columns!!
SELECT @OrderNo = 'order_no', --<--should be SELECT @OrderNo = order_no@CustomerID = 'customer_id'
FROM inserted
SELECT @TakerID = 'taker' --<<--taker not 'taker'FROM oe_hdr
WHERE order_no = @OrderNo
SELECT @TakerEmail = 'email_address' --<-- email_address
not 'email_address'
FROM users
WHERE id = @TakerID
SELECT @BillToName = 'bill2_name'
FROM invoice_hdr
WHERE customer_id = @CustomerID
Lowell
November 5, 2007 at 9:03 am
Ok, I think I understand what you are saying - so (remember, baby steps for the newb :blush:) how would I re-structure the trigger to accommodate that?
November 5, 2007 at 9:15 am
ok try this: i think the sql below will find everything that has a zero commission, all in one shot.
fromt here, you could construct a single email, or a bunch of individual emails.
SELECT
oe_hdr.order_no,
oe_hdr.customer_id,
oe_hdr.taker,
users.email_address,
invoice_hdr.bill2_name,
x.commission_cost,
'Order #' + CONVERT(varchar,oe_hdr.order_no) + ' for ' + invoice_hdr.bill2_name + ' has a $0 commission cost, please correct' As Subject
FROM oe_hdr
INNER JOIN (SELECT order_no, min(commission_cost) as commission_cost from oe_line group by order_no) X on oe_hdr.order_no=x.order_no
LEFT OUTER JOIN users on oe_hdr.taker = users.id
LEFT OUTER JOIN invoice_hdr on oe_hdr.customer_id=invoice_hdr.customer_id
WHERE x.commission_cost < 0.01
Lowell
November 5, 2007 at 9:39 am
No luck - that actually kinda freaked the app out a little. All of the sudden the other email alert trigger stopped working and I was getting hourglasses when I tried to save/work on the order
This is the trigger as I ran it with the last change:
[font="Courier New"]set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE TRIGGER [Zero_Commission_Cost_Notification] ON [dbo].[oe_hdr_salesrep]
WITH EXECUTE AS SELF
FOR INSERT, UPDATE
AS
DECLARE
@OrderNo as varchar(8),
@CustomerID as varchar(19),
@TakerID as varchar(15),
@TakerEmail as varchar(40),
@Recipients as varchar(50),
@Subject as varchar(80),
@BillToName as varchar(80),
@Commission as varchar(80)
SELECT
oe_hdr.order_no,
oe_hdr.customer_id,
oe_hdr.taker,
users.email_address,
invoice_hdr.bill2_name,
x.commission_cost,
'Order #' + CONVERT(varchar,oe_hdr.order_no) + ' for ' + invoice_hdr.bill2_name + ' has a $0 commission cost, please correct' As Subject
FROM oe_hdr
INNER JOIN (SELECT order_no, min(commission_cost) as commission_cost from oe_line group by order_no) X on oe_hdr.order_no=x.order_no
LEFT OUTER JOIN users on oe_hdr.taker = users.id
LEFT OUTER JOIN invoice_hdr on oe_hdr.customer_id=invoice_hdr.customer_id
WHERE x.commission_cost < 0.01
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Alerts',
@Recipients = @TakerEmail,
@Subject = @Subject
END[/font]
November 5, 2007 at 9:44 am
i should have explained...that is not for a trigger!
run that in QA...it should find the sample record you said you created.
if it works, then you can throw away the trigger, and just set that SQL up as a scheduled job to run once a day or something.
typically, you wan to try and avoid triggers that do emails, as an email server may take a while to completely respond...and if an email fails 9relaying not permitteed, bad email address, etc. it will ROLL back the data you were trying to insert....and it makes it very hard to determine why your invoices are disappearing!
by running that SQL as a once a day or every X hour job, to notiy your dept that there's a bad commision, you only have to worry about the job, and not data integrity.
Lowell
November 5, 2007 at 9:57 am
Be careful in that your trigger only works for one row inserts/updates. If someone inserts 2 rows, you might not get the right behavior.
Also, I'd really suggest you create a table, then load rows in the table showing problems. You can set a job that runs the email code, selects the relevant data from the table, then updates some column in the table to show it's sent, and sends the email.
More scalable, lets you track down issues, and not much more work than you've done here.
November 5, 2007 at 10:08 am
Lowell (11/5/2007)
i should have explained...that is not for a trigger!run that in QA...it should find the sample record you said you created.
if it works, then you can throw away the trigger, and just set that SQL up as a scheduled job to run once a day or something.
typically, you wan to try and avoid triggers that do emails, as an email server may take a while to completely respond...and if an email fails 9relaying not permitteed, bad email address, etc. it will ROLL back the data you were trying to insert....and it makes it very hard to determine why your invoices are disappearing!
by running that SQL as a once a day or every X hour job, to notiy your dept that there's a bad commision, you only have to worry about the job, and not data integrity.
I've got to admit that I really like this idea!
I ran this on our dev dbase (which is just the previous night's production dbase, restored to a new name) and it returned over 220,000 rows!!!!
Now, many of those are orders that have multiple line items on them so it's probably not bad as it sounds but still, if I were to run that in production and fire off that many emails my exchange admin would chase me down with a baseball bat.
Is there anyway to prevent it from pulling old orders? Can I tell it to only pull orders newer than say, 10/1/2007 using dbo.oe_hdr.order_date ?
Also, how would I tie in the email step? Would I just add the:
[font="Courier New"]BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Alerts',
@Recipients = @TakerEmail,
@Subject = @Subject
END[/font]
To the end of query, as part of the same job?
November 5, 2007 at 10:21 am
I believe that you don't have a complete overview of the trigger. Have you imagine the implication of this you are doing?
Trigger normally used for the data to be inserted,modified and/or deleted.
Where as you are using all records on a specified criteria. It should execute every time and send same mail multiple time to a single user who meet the criteria.
I 100% agree with the steve solution otherwise use temporay table INSERTED AND/OR DELETED generated to send data to that specific user who is added and/or modfied.
cheers
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply