November 14, 2017 at 9:14 am
Hello,
whats wrong here, when I update only one row everything is fine. But when there are more then one rows, the trigger and the update doesn´t work.ALTER TRIGGER [dbo].[xxatr_slauf_AufartChangeU] ON [dbo].[XXASLAuf]
AFTER UPDATE
AS
BEGIN
IF (
SELECT aufart
FROM inserted
) = 'U'
AND (
SELECT aufart
FROM deleted
) != 'U'
AND (
SELECT TourIntNr
FROM deleted
) IS NOT NULL
BEGIN
DECLARE @aufintnr VARCHAR(100)
SET @aufintnr = (
SELECT 'AufIntNR || AufArt
===================
' + convert(VARCHAR, aufintnr) + ' || ' + AufArt
FROM deleted
)
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mail-OR'
,@recipients = 'orenk@lis.eu'
,@subject = 'Treffer!'
,@body = @aufintnr
END
END
Thanks for help.
Olaf
November 14, 2017 at 9:36 am
That's because a trigger does not fire per row, but per query. If you UPDATE multiple rows, the trigger only fires once, and gives you both the inserted and deleted virtual tables to work with. It's up to your code to handle as many rows as were updated. You may want to look at letting your variable be the result of the same query, but structuring it slightly differently to include FOR XML PATH('') so that all the results appear concatenated. This will require that you probably use the STUFF function on the resulting string to remove the leading delimiter, and to provide the delimiter within your query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 15, 2017 at 5:29 am
Firt thing.
Don't send emails from a trigger.
Ever.
Too many things can go wrong with email related plumbing, and any one of them will cause your system get frozen due to some glitch.
Then - your trigger needs to consider 2 or more rows affected by the update.
Something like this:
ALTER TRIGGER [dbo].[xxatr_slauf_AufartChangeU] ON [dbo].[XXASLAuf]
AFTER UPDATE
AS
INSERT INTO dbo.EmailQueue
(profile_name , recipients ,subject,body)
SELECT 'Mail-OR', 'Mail-OR', 'orenk@lis.eu', 'orenk@lis.eu', 'Treffer!', 'AufIntNR || AufArt
===================
' + convert(VARCHAR, d.aufintnr) + ' || ' + d.AufArt
FROM deleted d
INNER JOIN inserted i on i.PK_COL = d.PK_COL
where i.aufart = 'U' and d.aufart <> 'U'
GO
Then create a job running around o'clock which would loop through records in dbo.EmailQueue and send out emails.
That would be one of those rare occasions when using a cursor would be totally appropriate.
_____________
Code for TallyGenerator
November 15, 2017 at 5:46 am
First solve the problem then write the code !
November 15, 2017 at 12:09 pm
ALTER TRIGGER [dbo].[xxatr_slauf_AufartChangeU]
ON [dbo].[XXASLAuf]
AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(aufart)
BEGIN
DECLARE @aufintnr VARCHAR(8000)
SET @aufintnr = ''
SELECT @aufintnr = @aufintnr + 'AufIntNR || AufArt ===================' +
convert(VARCHAR, d.aufintnr) + ' || ' + d.AufArt + '; '
FROM deleted d
INNER JOIN inserted i ON i.key_col = d.key_col
WHERE d.TourIntNr IS NOT NULL AND
i.aufart = 'U' AND
d.aufart <> 'U'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mail-OR'
,@recipients = 'orenk@lis.eu'
,@subject = 'Treffer!'
,@body = @aufintnr
END /*IF*/
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 15, 2017 at 12:21 pm
Sergiy - Wednesday, November 15, 2017 5:29 AMFirt thing.
Don't send emails from a trigger.
Ever.
Too many things can go wrong with email related plumbing, and any one of them will cause your system get frozen due to some glitch.Then - your trigger needs to consider 2 or more rows affected by the update.
Something like this:
ALTER TRIGGER [dbo].[xxatr_slauf_AufartChangeU] ON [dbo].[XXASLAuf]
AFTER UPDATE
AS
INSERT INTO dbo.EmailQueue
(profile_name , recipients ,subject,body)
SELECT 'Mail-OR', 'Mail-OR', 'orenk@lis.eu', 'orenk@lis.eu', 'Treffer!', 'AufIntNR || AufArt
===================
' + convert(VARCHAR, d.aufintnr) + ' || ' + d.AufArt
FROM deleted d
INNER JOIN inserted i on i.PK_COL = d.PK_COL
where i.aufart = 'U' and d.aufart <> 'U'
GOThen create a job running around o'clock which would loop through records in dbo.EmailQueue and send out emails.
That would be one of those rare occasions when using a cursor would be totally appropriate.
Or use Servic3e Broker to handle sending the emails asynchronously. I have done that in the past.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply