September 5, 2017 at 9:29 am
Hello all,
Thank you for any help given in advance, it is much appreciated.
Scenario - What I require is a database trigger that will send out an email when a specific column is updated, and for that email to contain a list of the rows that were updated accordingly.
The column that will be updated is a simple IsActive Flag. So if the IsActive Flag gets updated from 1 to 0 then I would like to be notified accordingly, and sent an email which shows me which row has been updated.
Is this something that is possible? Apologies for my lack of knowledge i am currently learning about database triggers which is something I have not touched before.
If someone could push me in the right direction it would be much appreciated.
Thanks,
Matt
September 5, 2017 at 9:35 am
This is possible yes, but who will be doing the update? You're either going to need to give ever Login/Role that might update the record permission to send emails via sp_send_dbmail or permission to impersonate a Login that has permission to do so.
Do you have to use a trigger? Is the record updated via an SP, or will users be running an UPDATE statement?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 5, 2017 at 11:17 am
mattdarvellwow - Tuesday, September 5, 2017 9:29 AMHello all,Thank you for any help given in advance, it is much appreciated.
Scenario - What I require is a database trigger that will send out an email when a specific column is updated, and for that email to contain a list of the rows that were updated accordingly.
The column that will be updated is a simple IsActive Flag. So if the IsActive Flag gets updated from 1 to 0 then I would like to be notified accordingly, and sent an email which shows me which row has been updated.
Is this something that is possible? Apologies for my lack of knowledge i am currently learning about database triggers which is something I have not touched before.
If someone could push me in the right direction it would be much appreciated.
Thanks,
Matt
Do you really want an email for EVERY SINGLE UPDATE? I don't know how large/active your database is, but you could easily be sending out thousands of emails a day. I would think that it would be better to log it in an audit table and then send out a daily report.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 6, 2017 at 3:09 am
We have users who have the necessary access to be able to update the table. The table would only be updated if a user runs an update statement. The table itself is only around 100 rows and the isactive column should not normally be changed. The idea here is that, for that reason we would want to be notified if the isactive flag has changed as it is not normal behaviour.
September 6, 2017 at 3:20 am
mattdarvellwow - Wednesday, September 6, 2017 3:09 AMWe have users who have the necessary access to be able to update the table. The table would only be updated if a user runs an update statement. The table itself is only around 100 rows and the isactive column should not normally be changed. The idea here is that, for that reason we would want to be notified if the isactive flag has changed as it is not normal behaviour.
So, as I asked before, do all these users/logins have permission to use sp_send_dbmail? What about Drew's suggestion of an audit table?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 6, 2017 at 6:59 am
Thom A - Wednesday, September 6, 2017 3:20 AMmattdarvellwow - Wednesday, September 6, 2017 3:09 AMWe have users who have the necessary access to be able to update the table. The table would only be updated if a user runs an update statement. The table itself is only around 100 rows and the isactive column should not normally be changed. The idea here is that, for that reason we would want to be notified if the isactive flag has changed as it is not normal behaviour.So, as I asked before, do all these users/logins have permission to use sp_send_dbmail? What about Drew's suggestion of an audit table?
All the users and logins will have permissions to use sp_send_dbmail, yes.
Drew's suggestion I am also not familiar with. I have used audit tables before but not actually created any myself. Again I would need pushing in the right direction as to how I would go about logging the just updated rows into an audit table.
Apologies for my lack of knowledge and thanks for any help.
September 6, 2017 at 7:45 am
This might help you get started. I've annotated it, so please ask any questions. This isn't tested, however, as I did this on our Sandbox Instance, and I haven't set DB Mail up.USE Sandbox;
GO
CREATE TABLE dbo.Record (ID int IDENTITY(1,1), OrderDate date, IsActive bit);
INSERT INTO dbo.Record (OrderDate, IsActive)
VALUES
('20170901',1),
('20170901',1),
('20170901',0),
('20170901',0),
('20170902',0),
('20170902',1),
('20170903',1),
('20170904',1),
('20170904',0),
('20170905',1),
('20170905',0),
('20170905',1),
('20170905',0),
('20170906',1);
GO
SELECT *
FROM Record;
GO
--Create a trigger to send emails.
--note, if a user does not have permission to send emails they will NOT be able to UPDATE records.
CREATE TRIGGER IsActive_Changed_Email ON dbo.Record
AFTER UPDATE
AS BEGIN
DECLARE @HTML varchar(MAX);
SET @HTML =
'<h2>The following records have been updated in the Record Table</h2>' + CHAR(10) +
'<table>' + CHAR(10) +
'<tr>' + CHAR(10) +
'<th>ID</th>' + CHAR(10) +
'<th>OrderDate</th>' + CHAR(10) +
'<th>OldIsActive</th>' + CHAR(10) +
'<th>NewIsActive</th>' + CHAR(10);
SET @HTML = @HTML +
CAST((SELECT i.ID AS td, '' + CHAR(10), i.OrderDate AS td, '' + CHAR(10),
d.IsActive AS td,'' + CHAR(10), i.IsActive AS td,'' + CHAR(10)
FROM inserted i
JOIN deleted d ON i.ID = d.ID
FOR XML PATH('tr')) AS varchar(MAX));
SET @HTML = REPLACE(@HTML,'
',CHAR(10)) + '</table>';
--Return the results below instead, however, you would put your sp_send_dbmail here.
SELECT @HTML;
END
GO
--Create an Audit Table
--Going to put this on a seperate Schema, so easily identified
CREATE SCHEMA History;
GO
--Create a Audit Table
CREATE TABLE History.Record (AuditID int IDENTITY(1,1), ID int, OrderDate date, IsActive bit, DateChanged datetime2(0));
GO
--Create trigger to insert the audit data
CREATE TRIGGER IsActive_Changed_Audit ON dbo.Record
AFTER UPDATE
AS BEGIN
INSERT INTO History.Record (ID, OrderDate, IsActive, DateChanged)
SELECT d.ID, d.OrderDate, d.IsActive, GETDATE()
--Note I only store the original IsActive value here
FROM deleted d;
END
GO
--Do some updates.
--These will return datasets containing the HTML to email, as I haven't used sp_send_dbmail in the trigger.
UPDATE dbo.Record
SET IsActive = 0
WHERE ID = 1;
GO
UPDATE dbo.Record
SET IsActive = 1
WHERE ID IN (3,4,13);
GO
--And also have a look at what's in the Audit table, you should be able to work out how to email data from this
--Even if you use the above code I've provided I've provided as a starting point
SELECT *
FROM History.Record;
GO
--Clean up
DROP TABLE History.Record;
DROP SCHEMA History;
DROP TABLE dbo.Record;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 6, 2017 at 9:56 am
I would not go for trigger. There are few other option, so can have better control and guaranteed email and logging
1. as soon as application updates the record, it can generate email.
2. have one more column in the table (or create a separate table) to flag the row for email notification, let the process which updates the column also mark this column if that row need to be notified. Have a scheduled job every few mins and send email for all those marked column and then set that column again to false
3. have nightly jobs and send email once only with list of all the rows changed
September 26, 2017 at 10:51 am
I am not sure I would necessarily use a trigger here.but rather a SSRS Report with a summary of the changes made during a period. You could create a subscription for the report and send it out at predefined times.
A trigger, as has been mentioned, is likely to be fired far too often to be practicable if the database sees a lot of activity in the area you are monitoring.
September 26, 2017 at 11:20 am
I would not be afraid of using a trigger to capture all event info you need (primary key, new column value, time, user login). Your description makes it clear that the column being updated is a rare event, not something happening thousands of times a day, and can only done by users with elevated permissions.
Sending an email from inside a trigger is usually a bad idea however. Triggers should ideally be very quick bits of code. What happens if someone does a manual UPDATE but skips the WHERE clause and updates 100 rows? Will your trigger try to send 100 emails, or send one and ignore the other 99 events? If all it has to do is insert 100 rows in the audit table, it won't cause a noticeable delay.
The idea that a job will come along every few minutes (or whatever acceptable interval you like) and create one email with all recent changes is much better than sending email from triggers.
September 28, 2017 at 6:56 am
mattdarvellwow - Tuesday, September 5, 2017 9:29 AMHello all,Thank you for any help given in advance, it is much appreciated.
Scenario - What I require is a database trigger that will send out an email when a specific column is updated, and for that email to contain a list of the rows that were updated accordingly.
The column that will be updated is a simple IsActive Flag. So if the IsActive Flag gets updated from 1 to 0 then I would like to be notified accordingly, and sent an email which shows me which row has been updated.
Is this something that is possible? Apologies for my lack of knowledge i am currently learning about database triggers which is something I have not touched before.
If someone could push me in the right direction it would be much appreciated.
Thanks,
Matt
You can use the OUTPUT clause to get the rows updated into a temp table and then use it to send email using sp_send_dbmail sproc
use tsql2012
Select
custid+0 ascustid,
companyname,
contactname,
contacttitle,
address,
city,
region,
postalcode,
country,
phone,
fax
into #temp from Sales.Customers where 1=0
begin tran
update Sales.Customers set city = ''
output deleted.* into #temp
where custid = 2
if @@ROWCOUNT >0
begin
Select * from #temp
/*your logic to send email regarding changed rows*/
end
else
begin
select 'no row updated'
end
rollback
First solve the problem then write the code !
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply