January 11, 2020 at 10:55 pm
This is very cowboy, I know, but it's late and the people who can do it properly are sleeping and I need a temp solution until morning... Also, haven't worked with triggers much, but have a basic understanding and know what's in the inserted and deleted tables.
Situation is that I have a table (Queue) acting as a messaging queue for outgoing messages. There are various processes (some of them unknown to me) that insert messages into this table and there is a Windows service running that reads the queue and sends these messages (Emails and SMSs). I've just picked up that there is some process gone rogue (luckily only for the last few hours) that is causing the same message to be sent to the same recipient over and over again (±every 10 minutes). I've stopped the messaging service for a bit, but this is less than ideal to say the least.
I would therefore like to put a trigger on the Queue table that identifies the rogue messages (this is easy enough from the text and it's non-critical if I get some false positives) prevents them from being inserted (or deletes them as soon as they are) and instead inserts them in another table (tempQDump) that I can use to analyze the mess later. This will be a temporary measure until we can determine where the rogue messages are originating.
I already have the insert into the tempQDump table sorted out based on a select from inserted. What I'm having trouble with is how to prevent the insert into Queue. Do I delete from inserted, delete from the base table, or is there some other way?
------------------------------------------
Anton
www.sqlsqllittlestar.com
January 12, 2020 at 12:48 am
Hi Anton,
I can't see if you're using an AFTER INSERT or INSTEAD OF INSERT trigger. It sounds like you're having problems with an AFTER INSERT trigger, because you're describing that the problem rows are being added to the Queue table.
I believe you want an INSTEAD OF INSERT trigger; when an INSERT INTO Queue is attempted, it would run this trigger instead of inserting into the real table, which you can then use to programatically filter what actually makes it to the Queue table. Within the INSTEAD OF trigger, when you do an INSERT INTO Queue, it would actually do the insert, whereas if an insert into the Queue table is attempted from anywhere outside that trigger, it would run this trigger instead.
You can't modify the rows in the INSERTED and DELETED virtual tables (you'll get "The logical tables INSERTED and DELETED cannot be updated."). You would usually use these to determine what rows you want to INSERT/DELETE/UPDATE into the base table, then run the operation you want based on that against the base table from within the trigger.
USE TEMPDB;
IF OBJECT_ID('tempdb..Scores') IS NOT NULL
DROP TABLE Scores
CREATE TABLE Scores (
Score INT
)
IF OBJECT_ID('tempdb..RejectScores') IS NOT NULL
DROP TABLE RejectScores
CREATE TABLE RejectScores (
Score INT
)
GO
CREATE TRIGGER TRG_SCORES_INSTEAD_INSERT
ON Scores INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Scores(Score)
SELECT INSERTED.Score
FROM INSERTED
WHERE INSERTED.Score / 2.0 = 1
INSERT INTO RejectScores(Score)
SELECT INSERTED.Score
FROM INSERTED
WHERE INSERTED.Score / 2.0 <> 1
END
GO
INSERT INTO Scores (Score)
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
GO
SELECT Scores.Score
FROM Scores
SELECT RejectScores.Score
FROM RejectScores
You do need to be cautious about using triggers and performance - putting a trigger on a messaging queue table with high performance requirements has potential to cause issues.
Andrew
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply