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