Robin35 (4/16/2013)
Thanks Steve,Lowell,L'eomot for your help and patience ....script is working fine now....i'm posting the entire script for people who might need it in future....--/****** Object: Table [dbo].[logger] Script Date: 04/15/2013 13:07:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[logger_all](
[Body] [varchar](500) NULL,
[ConfigSetID_Ins] [varchar](100) NULL,
ConfigSetID_Del varchar(100),
[Subject] [varchar](104) NULL,
[Name] [varchar](200) NULL,
[Version] [varchar](100) NULL,
[Timestamp] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE TRIGGER [dbo].[ConfigTrigger_New]
ON [dbo].[ConfigSet]
AFTER Update, insert, delete
AS
DECLARE @ConfigSetID_Ins varchar(100)
DECLARE @ConfigSetID_Del varchar(100)
DECLARE @Body varchar(500)
Declare @Subject varchar(104)
Declare @Name varchar(200)
Declare @Version varchar(100)
Declare @Timestamp varchar(100)
declare @optype tinyint = 0;
if exists (select * from inserted) set @optype = @optype+1
if exists (select * from deleted) set @optype = @optype+2
BEGIN
set @Subject =
case @optype
when 1 then 'New row
inserted into ConfigSet table in XXXXXX'when 2 then 'Row
deleted from ConfigSet table in XXXXXXX'when 3 then 'Row
modified in ConfigSet table in XXXXXX'else 'Nothing Happened'
end ;
SELECT @ConfigSetID_Ins = i.ConfigSetID, @Name = i.Name, @Version = .[Version],
@Timestamp = i.[Timestamp]
from inserted i --join ConfigSet C on I.ConfigSetID = C.ConfigSetID
SELECT @ConfigSetID_Del = d.ConfigSetID, @Name = d.Name, @Version = [d].[Version],
@Timestamp = d.[Timestamp]
from deleted d-- join ConfigSet C on d.ConfigSetID = C.ConfigSetID
if @optype = 1 or @optype = 3
select @Body = 'New record has been Updated in ConfigSet table in XXXXXX, Following are the details ' + char(13)
+ 'ConfigSetID : ' + ISNULL(@ConfigSetID_Ins,'[Missing ConfigSet]') + CHAR(13)
+ 'Name : ' + ISNULL(@Name ,'[Missing Name]') + CHAR(13)
+ 'Version : ' + ISNULL(@Version,'[Missing Version]') + CHAR(13)
+ 'TimeStamp : ' + ISNULL(@Timestamp,'[Missing Timestamp]') + CHAR(13)
from inserted i ;
if @optype = 2 --or @optype = 3
select @Body = 'New record has been Updated in ConfigSet table in XXXXX, Following are the details ' + char(13)
+ 'ConfigSetID : ' + ISNULL(@ConfigSetID_Del,'[Missing ConfigSet]') + CHAR(13)
+ 'Name : ' + ISNULL(@Name ,'[Missing Name]') + CHAR(13)
+ 'Version : ' + ISNULL(@Version,'[Missing Version]') + CHAR(13)
+ 'TimeStamp : ' + ISNULL(@Timestamp,'[Missing Timestamp]') + CHAR(13)
from deleted d ;
INSERT INTO dbo.logger_all SELECT @Body, @ConfigSetID_Ins, @ConfigSetID_Del, @Subject, @Name, @Version, @Timestamp
EXEC msdb..sp_send_dbmail
@profile_name = 'XXXXXX',
@recipients = 'XXXX@.com',
@subject = @subject,
@body = @body
END
Actually, while I'm here...
If I'm reading the code correctly, the code has a bit of a flaw in it that you might want to consider. If someone inserts, updates, or deletes more than 1 row in the same insert, update, or delete, what do you want to be contained in the email? I suspect it will be that you want more than 1 row?
--Jeff Moden
Change is inevitable... Change for the better is not.