October 12, 2009 at 9:48 pm
HERE IS THE CODE, IT WORKS WHEN I UPDATE OR INSERT JUST ONE, BUT WHEN I UPDATE MULTIPLE ROWS AT THE SAME TIME I GET THIS ERROR: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I need to send all rows affected in one mail if possible or one mail by row, i need this trigger work in the two situations, when one row is affected, or when multiple rows are affected. Sorry my english is not so good, Mexican Guy :S
USE [ordenesSQL]
GO
/****** Object: Trigger [dbo].[NuevaFactura] Script Date: 10/12/2009 20:57:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[NuevaFactura] ON [dbo].[ORDENES] AFTER INSERT, UPDATE AS
DECLARE @noorden int
DECLARE @cliente varchar(255)
DECLARE @facturado bit
SET @noorden = (SELECT NOORDEN FROM inserted)
SET @cliente = (SELECT NOMBRECLIENTE FROM inserted)
SET @facturado = (SELECT FACTURADO FROM inserted)
IF @facturado = 'True'
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'Se ha facturado la orden No. ' + CAST(@noorden as varchar(10)) + ' del cliente ' + @cliente + '.'
--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail @recipients=N'phanatico@live.com.mx', @body= @msg, @subject = @msg, @profile_name = 'Grupo Grafico SQL Mail'
END
October 12, 2009 at 10:42 pm
No one?
October 12, 2009 at 11:01 pm
HEre is where your problem is:
SET @noorden = (SELECT NOORDEN FROM inserted)
SET @cliente = (SELECT NOMBRECLIENTE FROM inserted)
SET @facturado = (SELECT FACTURADO FROM inserted)
You have a choice, you can either assemble one BIG email or send one for each..
Provided you don't get too man rows inserted..
SET @noorden = (SELECT NOORDEN FROM inserted)
SET @cliente = (SELECT NOMBRECLIENTE FROM inserted)
SET @facturado = (SELECT FACTURADO FROM inserted)
DECLARE @wktable TABLE ( noorden int, cliente varchar(255) )
INSERT @wktable
( noorden, cliente )
SELECT noorden, cliente
FROM inserted
WHERE facturado = 'True'
Now depending on whether you want one big or multiple small you either assemble a big string from this or do some kind of a loop on the @wktable to send the emails.
Clear?
CEWII
October 13, 2009 at 12:12 am
well is clear what you mean, now im going to find how to do a loop in the table to get the each row cause im new on this, i don't know very well the syntax, in another language, i will do something like this:
for each row in @wktable {
@mailbody = @mailbody + @noorden + @cliente + <br />
}
then send the mail with @mailbody
but how to do that in sql. Anyway thanks, you helped me. but if someone can help more i'll be here looking
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply