Table column modification generation alert through mail

Chandra Mohan Kanithi
Chandra Mohan Kanithi used Ask the Experts™
on
Scenario:

Table : Supp
Columns:
AppUser
SQLUser
DateCreated
DateModified
SuppCode
SuppDesc
Address
SuppType

Whenever application changing 'SuppDesc' column data in 'Supp' Table, we need details include hostname (user modifying application from his desktop) through email alert.

Version : SQL Server 2008 R2.

Please help me with solution.

Thanks,
Chandra
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
A simple AFTER UPDATE Trigger should help for your scenario..
Please be noted that this will work if
1. All Primary key Column and table names are properly replaced in the script below.
2. Database Mail is already configured in the SQL Instance.
3. In the Send mail procedure, Profile name and email id are replaced properly.
4. If you want to prevent UPDATE on SuppDesc column, then you should use "INSTEAD OF UPDATE" trigger in place for "AFTER UPDATE" trigger provided below.
CREATE TRIGGER [dbo].[Supp_SuppDesc_UPDATE]
ON Supp
AFTER UPDATE
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @pk INT
       DECLARE @Body VARCHAR(1000)
	   DECLARE @host_name varchar(100) = HOST_NAME();
	   DECLARE @Program_name varchar(100) = APP_NAME();
	   DECLARE @user_name varchar(100) = SUSER_NAME();
 
       SELECT @pk = INSERTED.primary_key --UPDATE Primary_key with Primary key column on Supp Table
       FROM INSERTED

	   SELECT @Body = 'Supp Table Modified on Primary Key value: ' + Cast(@pk as varchar(50)) + ',Connecting Server: ' + @host_name + ', Application_Name: ' + @Program_name + ',User_Name: ' + @user_name
 
       IF UPDATE(SuppDesc)
       BEGIN
              EXEC msdb.dbo.sp_send_dbmail  
					@profile_name = 'Replace ur DB Profile Name',  
					@recipients = 'Ur_email_id',  
					@body = @Body,  
					@subject = 'SuppDesc Column Modified in Supp Table' ;
       END

END

Open in new window

Chandra Mohan KanithiSenior Principal Consultant - Database

Author

Commented:
Thanks Raja. The solution has been implemented successfully.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Welcome, glad to help..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial