SQL trigger per Order no..

Question

Post reply

SQL trigger per Order no..

dopydb

SSCertifiable

Points: 6687
More actions
June 3, 2010 at 4:26 am

#235891

i all
i have the below trigger, which sends an email when an order is released for picking. that works fine.
the problem is it sends an email for every line that is on the order,(eg if an order has 5 items it has 5 lines) i only want 1 email per order no.
how could i go about this?
USE [CompanyT]
GO
/****** Object: Trigger [dbo].[QC_email] Script Date: 06/03/2010 09:50:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[QC_email] ON [dbo].[PICK_RELEASED_ORDERS] AFTER Insert
AS
declare @msg VARCHAR(MAX)
declare @CUSTOMER char (7)
declare @SalesOrder char (6)
SET @CUSTOMER = (SELECT CUSTOMER FROM inserted)
SET @SalesOrder = (SELECT SALES_ORDER FROM inserted)
SELECT @msg = ISNULL(@msg+CHAR(10),'')
+ 'Picknote has been created for QC assigned Customer"' + CUSTOMER
+'" the order No is "' +SALES_ORDER+'" Please arrange to check the order before it has despatched!'
FROM Inserted
if @CUSTOMER in('MA102','WY111','MA102','CA016','FO021','CO079','BE032','PL003','ST101','PL014','CO301','BA033'
,'AT004','TR044','CO062','WY153','HI020','CO008','PR002','GO035','FL063','LE005','C0030','MA102','HO623'
,'GR014','WY152','BE011'
)
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'email@address.com;',
@subject = 'QC ALERT - ORDER BEING PICKED',
@body = @msg

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

dopydb SSCertifiable Points: 6687 More actions · Answer 1

hmm, i cant do this can i? unless i update after the first email?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

you can do one thing

1. create a intermediate table

2. insert

SELECT @msg = ISNULL(@msg+CHAR(10),'')
+ 'Picknote has been created for QC assigned Customer"' + CUSTOMER
+'" the order No is "' +SALES_ORDER+'" Please arrange to check the order before it has despatched!'
FROM Inserted

with the help of existing DML INSERT into intermediate table.

and create new DML INSERT trigger on this new table which will fire mail on every insert. here you can handle that duplicacy

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

dopydb SSCertifiable Points: 6687 More actions · Answer 3

i have free fields on the table in question, i think i will simply run with that,

thanks for advice tho!

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 4

I appears that your table [dbo].[PICK_RELEASED_ORDERS] contain one row per line item, rather than one row per order -- is that correct? Also, it appears then that the code that drops the rows into that table drops them one at a time (rather than in a batch insert, as in the following: select * into [dbo].[PICK_RELEASED_ORDERS] from [dbo].[someothertable]

A trigger gets fired once per 'batch'. The 'pseudo table' INSERTED then contains one row for each row inserted in the batch. If what I suspect (above) is true, then the trigger is getting fired multiple (5) times, once for each row inserted; hence, it is sending 5 emails.

Does that help clarify at all?

Rob Schripsema
Propack, Inc.

dopydb SSCertifiable Points: 6687 More actions · Answer 5

hi Rob

yea thats whats happening it seems,

i thought maybe it was being updated as a batch first of all as well but again as you say its done line by line

would be no big deal only the 3rd party who support our ERP system do not like tables etc geting updated with userdefined data, and im still the new guy 🙁

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

malachyrafferty (6/3/2010)
as you say its done line by line

DML triggers work on per line basis

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)