July 7, 2009 at 8:42 am
I have a simple table that is just an inventory of my sql servers. The table is structured like this:
[svr_id] [int] IDENTITY(1,1) NOT NULL,
[svr_name] [varchar](50) NOT NULL,
[svr_ip] [varchar](50) NULL,
[svr_ver] [varchar](50) NULL,
[svr_pur] [varchar](50) NULL,
[svr_sa] [varchar](50) NULL,
[svr_os] [varchar](50) NULL
What I want to do is write a SQL Trigger that when svr_name is updated, it will email me with the old name and the new name. I have SA's that are updating this table, as they rename servers and just so that I know when it happens, I would like to have this trigger.
Can somebody help?
Thank you for your time,
Jordon
July 7, 2009 at 8:47 am
What edition of SQL Server, and what have you written so far to meet your requirements?
July 7, 2009 at 9:00 am
It is SQL 2005 and I haven't written anything yet.
July 7, 2009 at 9:23 am
jordon.shaw (7/7/2009)
It is SQL 2005 and I haven't written anything yet.
Okay, but what edition?
July 7, 2009 at 9:35 am
Sorry! It's Standard Edition. Let me show you what I've wrote so far, which I think will work, except I'm getting a syntax error on @svr_id in the line that has SET @body = 'Server with ID=' @svr_id Any ideas on that one?
CREATE TRIGGER SQL_update
ON COF_sqlsrvs
FOR UPDATE
AS
declare @svr_id varchar(10)
declare @body varchar(2000)
declare @oldsvr_name varchar(50)
declare @newsvr_name varchar(50)
SELECT @svr_id = svr_id,
@oldsvr_name = d.svr_name
FROM deleted d
SELECT @newsvr_name = svr_name
FROM inserted
SET @body = 'Server with ID=' @svr_id
'has been updated with previous Server Name is'
@oldsvr_name 'and the new Server Name is'
@newsvr_name
EXEC master..xp_sendmail
@recipients = 'jordon.shaw@franklintn.gov',
@subject = 'Server Name Updated',
@message = @body
GO
July 7, 2009 at 10:18 am
Ok, looks like the mail function has been replace. So, I've updated the code and when I take the varibles out of the message, it works perfectly; however, when putting them in the message, I'm still getting a syntax error on @svrid. Here is my current code:
USE [COF_sql]
GO
/****** Object: Trigger [dbo].[SQL_update] Script Date: 07/07/2009 11:02:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[SQL_update]
ON [dbo].[COF_sqlsrvs]
FOR UPDATE
AS
declare @svrid varchar(10)
declare @message varchar(2000)
declare @oldsvr_name varchar(50)
declare @newsvr_name varchar(50)
SELECT @svrid = svr_id,
@oldsvr_name = d.svr_name
FROM deleted d
SELECT @newsvr_name = svr_name
FROM inserted
SET @message = 'Server with ID=' @svrid
'has been updated with previous Server Name is'
@oldsvr_name 'and the new Server Name is'
@newsvr_name
EXEC msdb.dbo.sp_send_dbmail
@profile = 'SQL Alerts',
@recipients = 'jordon.shaw@franklintn.gov',
@subject = 'Server Name Updated',
@body = @message
July 7, 2009 at 10:35 am
Nevermind, I have figured this out. So for anybody else looking to do it, this is what you need to do:
USE [COF_sql]
GO
/****** Object: Trigger [dbo].[SQL_update] Script Date: 07/07/2009 11:02:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[SQL_update]
ON [dbo].[COF_sqlsrvs]
FOR UPDATE
AS
declare @svrid varchar(10)
declare @message varchar(2000)
declare @oldsvr_name varchar(50)
declare @newsvr_name varchar(50)
SELECT @svrid = svr_id,
@oldsvr_name = d.svr_name
FROM deleted d
SELECT @newsvr_name = svr_name
FROM inserted
SET @message = 'Server with ID=' + @svrid +
'has been updated with previous Server Name is'
+ @oldsvr_name + 'and the new Server Name is'
+ @newsvr_name
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Alerts',
@recipients = jordon.shaw@franklintn.gov',
@subject = 'Server Name Updated',
@body = @message
July 7, 2009 at 12:07 pm
Perfect, just what I wanted to see. thank you for posting your final code as well for others to view.
July 9, 2009 at 2:06 am
I am doing something very similar - your example works well - but there is an issue in that some of my fields slected contain NULL values. If these are selected as being updated then the email is sent is blank / empty even if the other fields are populated with data- any ideas on how to handle the NULLS?
thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply