Table column modification generation alert through mail

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
Chandra Mohan KanithiSenior Principal Consultant - DatabaseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chandra Mohan KanithiSenior Principal Consultant - DatabaseAuthor Commented:
Thanks Raja. The solution has been implemented successfully.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome, glad to help..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.