May 22, 2023 at 7:34 am
Hi,
I have following requirement.
There is Sql server DB table OrderDetails. It has date, UserName , Details, OrderID columns.
Case 1: I want to send email alert whenever any new record is inserted then email should be sent with below details.
The new records date and time of insertion, UserName who inserted the record, message like " A new record was inserted to OrderDetails table"
Case 2: For update in DB table OrderDetails, then email should be sent like.
The new records date and time of updation, UserName who updated the records, message like " A new record was updated to OrderDetails table"
Case 3: For Delete in DB table OrderDetails, then email should be sent like.
The new records date and time of Deletion, UserName who deleted the records, message like " A new record was deleted into OrderDetails table"
Can someone please assist me with sql script?
May 22, 2023 at 12:09 pm
What you're looking for are the INSERTED and DELETED tables that are available within a trigger. For an INSERT operation, you'll only see detail in the INSERTED table. For a DELETE operation, you'll only see detail in the DELETED table. For an UPDATE, you'll see it both tables, giving you the information added in the INSERTED table and the information that was modified in the DELETED table. The queries to retrieve information from these tables are extremely straight forward as shown in the examples at the link. Even if multiple rows were added, removed or modified, they'll show in the INSERTED and DELETED tables.
Now, a word on triggers & email. If this table is high volume at all, this could be an extremely problematic process for your system. You might be better off looking at something like Change Data Capture or using Extended Events to capture queries and their parameters.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2023 at 1:35 pm
I'd consider writing the output from these triggers into a custom 'MessageQueue' table and having a separate periodic process running which picks up unprocessed rows from this table, sends the necessary e-mails and then marks the entries as processed.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 22, 2023 at 3:15 pm
Thanks for you reply.
You mean I need to create two separate triggers lime below?
CREATE TRIGGER TR_OrderDetails
ON dbo.OrderDetails
AFTER INSERT
AS
SELECT *
FROM Inserted;
GO
CREATE TRIGGER TR_OrderDetails_Del
ON dbo.OrderDetails
AFTER DELETE
AS
SELECT *
FROM Deleted;
GO
May 22, 2023 at 3:39 pm
No, a single trigger can be used to cover multiple operations
CREATE TRIGGER ON ...
AFTER INSERT, UPDATE, DELETE
<trigger logic here>
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply