Thanks a lot Lowell and E'Loment....it worked like a charm....but i have some problems here....

the output what we are getting is the updated items....but the requester is looking for old item i mean records before the update....and i'm planning to create insert,update and delete...i have used your code and create a trigger like this....

For delete it isnt working....no output in logger_all table, that means @body isnt working...

for insert/update its working.........but for update i'm looking for old record...not the new column that was updated....

Please let me know your thoughts..

Here's the code

--/****** 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](

--[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

ALTER 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 XXXXXX'

when 3 then 'Row modified in ConfigSet table in XXXXXX'

else 'This should never happen'

end ;

SELECT @ConfigSetID_Ins = C.ConfigSetID, @Name = c.Name, @Version = [c].[Version],

@Timestamp = c.[Timestamp]

from inserted i join ConfigSet C on I.ConfigSetID = C.ConfigSetID

SELECT @ConfigSetID_Del = C.ConfigSetID, @Name = c.Name, @Version = [c].[Version],

@Timestamp = c.[Timestamp]

from deleted d join ConfigSet C on d.ConfigSetID = C.ConfigSetID

--SET @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)

--if @optype = 1 or optype = 3 set @Body = 'New rows:'

if @optype = 1 or @optype = 3

select @Body = --@Body + char(13) +

ISNULL(@ConfigSetID_Ins,'[Missing ConfigSet]') + CHAR(13) +

ISNULL(@Name ,'[Missing Name]')+ CHAR(13) +

ISNULL(@Version,'[Missing Version]')+ CHAR(13) +

ISNULL(@Timestamp,'[Missing Timestamp]')+ char(13)

from inserted i ;

if @optype = 2 --or @optype = 3

select @Body = --@Body + 'Old Records:' + char(13) +

ISNULL(@ConfigSetID_Ins,'[Missing ConfigSet]') + CHAR(13) +

ISNULL(@Name ,'[Missing Name]') + CHAR(13) +

ISNULL(@Version,'[Missing Version]') + CHAR(13) +

ISNULL(@Timestamp,'[Missing Timestamp]') + char(13)

from deleted d ;

insert INTO logger_all SELECT @Body, @ConfigSetID_Ins, @ConfigSetID_Del, @Subject, @Name, @Version, @Timestamp

--EXEC msdb..sp_send_dbmail

--@profile_name = 'sqlserverdba',

-- @recipients = 'venkat@.com',

-- @subject = @subject,

-- @body = @body

--END