April 12, 2013 at 12:12 pm
krishnavenkat16 (4/12/2013)
Yes i have updated it and create trigger in my table. Deleted, inserted the record ...same issue....getting email but it has nothing in it except the subject
Can you post your code? Can't really offer much advice otherwise. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 12, 2013 at 12:23 pm
Just updated Lowell's code with my table and columns....in my previous post you can see table structure....please let me know your thoughts....
CreateTRIGGER TR_NOTIFICATIONS
ON ConfigSet FOR Update, insert, delete
AS
BEGIN
SET NOCOUNT ON
DECLARE @CAPTUREDSTRING VARCHAR(max)
SELECT @CAPTUREDSTRING = [DeletedItems]
FROM (
SELECT TOP 1 GETDATE()As TheDate,stuff(( SELECT ',' + Name
FROM INSERTED s2
--WHERE s2.WHATEVERID= s1.WHATEVERID --- must match GROUP BY below
WHERE 1 = 1
ORDER BY Name
FOR XML PATH('')
),1,1,'') as [DeletedItems]
FROM DELETED s1
GROUP BY s1.ConfigSetID --- without GROUP BY multiple rows are returned
ORDER BY s1.ConfigSetID) myAlias
--now email the results.
declare @htmlbody varchar(max)
set @htmlbody = 'Notification on ConfigSet Table '
+ CONVERT( VARCHAR( 20 ), GETDATE(), 113 )
+ '
<P> The following new items were updated on the table:<P>'
+ @CAPTUREDSTRING
+ '
'
SELECT @htmlbody =
'Notification on ConfigSet Table '
+ CONVERT( VARCHAR( 20 ), GETDATE(), 113 )
+ 'EventDate :' + CONVERT(VARCHAR(30),getdate() ,112) + '
' + CHAR(13) + CHAR(10)
+ 'DBName :' + DB_NAME() + '
' + CHAR(13) + CHAR(10)
+ 'CurrentUser :' + CURRENT_USER + '
' + CHAR(13) + CHAR(10)
+ 'HostName :' + HOST_NAME() + '
' + CHAR(13) + CHAR(10)
+ 'ApplicationName :' + APP_NAME() + '
' + CHAR(13) + CHAR(10)
+ 'ProcedureName :' + ISNULL(OBJECT_NAME(@@PROCID) ,'') + '
' + CHAR(13) + CHAR(10)
+ 'Userid :' + CONVERT(VARCHAR(30),USER_ID() ) + '
' + CHAR(13) + CHAR(10)
+ 'UserName :' + USER_NAME() + '
' + CHAR(13) + CHAR(10)
+ 'sUserid :' + CONVERT(VARCHAR(30),SUSER_ID() ) + '
' + CHAR(13) + CHAR(10)
+ 'sUserName :' + SUSER_SNAME() + '
' + CHAR(13) + CHAR(10)
+ '[ORIGINAL_LOGIN] :' + ORIGINAL_LOGIN() + '
' + CHAR(13) + CHAR(10)
+ 'net_transport :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('net_transport'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'protocol_type :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('protocol_type'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'auth_scheme :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('auth_scheme'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'local_net_address :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('local_net_address'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'local_tcp_port :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('local_tcp_port'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'client_net_address :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('client_net_address'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'physical_net_transport :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('physical_net_transport'),'') ) + '
' + CHAR(13) + CHAR(10)
+ @CAPTUREDSTRING --this has the list of values
+ '
'
EXEC msdb..sp_send_dbmail
@profile_name = 'xxxxxx',
@recipients = 'xxxxxxx',
@subject = 'Changes have been made to ConfigSet Table',
@body = @htmlbody,
@body_format = 'HTML'
END
GO
April 12, 2013 at 12:28 pm
your issue is this part:
SELECT @CAPTUREDSTRING = [DeletedItems]
FROM (
SELECT TOP 1 GETDATE()As TheDate,stuff(( SELECT ',' + Name
FROM INSERTED s2
--WHERE s2.WHATEVERID= s1.WHATEVERID --- must match GROUP BY below
WHERE 1 = 1
ORDER BY Name
FOR XML PATH('')
),1,1,'') as [DeletedItems]
FROM DELETED s1
GROUP BY s1.ConfigSetID --- without GROUP BY multiple rows are returned
ORDER BY s1.ConfigSetID) myAlias
and this:
+ @CAPTUREDSTRING --this has the list of values
that has to be modified to gather your specific data...and on INSERT it would return a null(there is no data int he DELETED special table)...so adding a null plus the whoodunnit string created at the bottom will return an empty email.
remove it completely, to prove to yourself it works.
then modify that to gather and return whatever data you really need to report.
hint: the data will be different for insert vs update vs delete: so make sure you modify it accordingly. build three different strings, depending on whether it is insert/update/delete is what i would recommend...plus you'l learn exactly how to modifyt he trigger so you can support it in the future.
Lowell
April 12, 2013 at 1:32 pm
I would again suggest you revisit this idea. It's easy to lose emails, and potentially miss auditing issues here.
If you want to capture this, I would say you should insert this into a table. You can always read the table to send emails, but this way if email is down or there is some issue with your system, you can catch the issue here.
April 12, 2013 at 1:45 pm
I have to agree with Steve on this one.
April 12, 2013 at 3:01 pm
Hi Steve, i like your idea....i will do that...please see new code...its working partially only if i put column ConfigSetID in @body...but if i add Name,Version and Timestamp in @body... i'm unable to update the table....not sure whats the issue...i'm not using cast or convert operator
USE [TriggerTest]
GO
/****** Object: Trigger [dbo].[Newrecordadded] Script Date: 04/12/2013 15:34:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create TRIGGER [dbo].[ConfigTrigger]
ON [dbo].[ConfigSet]
AFTER INSERT, DELETE, UPDATE
AS
DECLARE @ConfigSetID_Ins varchar(100)
DECLARE @ConfigSetID_Del varchar(100)
DECLARE @Body varchar(500)
Declare @Subject varchar(104)
Declare @Name varchar(64)
--Declare @Version smallint
--Declare @Timestamp binary(8)
BEGIN
Select @Subject = 'New record has been added in ConfigSet table in xxxxxxx';
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
--from DELETED d join ConfigSet C on d.ConfigSetID = C.ConfigSetID
SET @body = 'New record '+'''' + @ConfigSetID_Ins + ',' + @Name + ',' --+ @Timestamp
+ ''''+' has been added to '+'CongifSet table in xxxxx'
EXEC msdb..sp_send_dbmail
@profile_name = 'xxxxxx',
@recipients = 'venkat@.com',
@subject = @subject,
@body = @body
END
GO
after creating this trigger....i tried modifying table to get email...but its not allowing me to update..i got this error..please suggest
Msg 245, Level 16, State 1, Procedure ConfigTrigger, Line 27
Conversion failed when converting the varchar value ' , ' to data type int.
April 12, 2013 at 3:06 pm
SET @body = 'New record '+'''' + @ConfigSetID_Ins + ',' + @Name + ',' --+ @Timestamp
+ ''''+' has been added to '+'CongifSet table in xxxxx'
you must use CAST or CONVERT for every column that is not a varchar, if you want them to be concatenated together. CONVERT(VARCHAR,@ConfigSetID_Ins) for example.
Lowell
April 13, 2013 at 3:53 am
Lynn Pettis (4/12/2013)
I have to agree with Steve on this one.
Me too. I've always put stuff in a table and had a scheduled job which reads the table and sends emails.
Tom
April 14, 2013 at 8:23 pm
Lowell, tried your suggestion...added convert but still it isnt working.....
Steve, i will create the table for security, but first i need to get this thing work....then i can create table and work on that stuff....
please let me know ur thoughts.....
USE [TriggerTest]
GO
/****** Object: Trigger [dbo].[ConfigTrigger] Script Date: 04/14/2013 21:57:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[ConfigTrigger]
ON [dbo].[ConfigSet]
AFTER INSERT, DELETE, UPDATE
AS
DECLARE @ConfigSetID_Ins varchar(100)
--DECLARE @ConfigSetID_Del varchar(100)
DECLARE @Body varchar(500)
Declare @Subject varchar(104)
Declare @Name varchar(100)
Declare @Version int
Declare @Timestamp binary(8)
BEGIN
Select @Subject = 'New record has been added in ConfigSet table in XXXXXX';
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
--from DELETED d join ConfigSet C on d.ConfigSetID = C.ConfigSetID
SET @body = 'New record '+'''' + Convert( varchar,@ConfigSetID_Ins) + ',' + @Name + ',' + Convert( varchar,@Version) + ' , ' +
Convert( varchar,@Timestamp) + ''''+' has been added to '+'CongifSet table in XXXX'
EXEC msdb..sp_send_dbmail
@profile_name = 'sqlserverdba',
@recipients = 'venkata@.com',
@subject = @subject,
@body = @body
END
GO
April 15, 2013 at 8:45 am
Basic debugging needed here. Please do this first.
create table logger
( Body varchar(500)
, Subject varchar(104)
, Name varchar(100)
, Version int
, Timestamp binary(8)
)
Use this to debug your trigger.
Then set the trigger like this:
ALTER TRIGGER [dbo].[ConfigTrigger]
ON [dbo].[ConfigSet]
AFTER INSERT, DELETE, UPDATE
AS
DECLARE @ConfigSetID_Ins varchar(100)
--DECLARE @ConfigSetID_Del varchar(100)
DECLARE @Body varchar(500)
Declare @Subject varchar(104)
Declare @Name varchar(100)
Declare @Version int
Declare @Timestamp binary(8)
BEGIN
Select @Subject = 'New record has been added in ConfigSet table in XXXXXX';
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
--from DELETED d join ConfigSet C on d.ConfigSetID = C.ConfigSetID
SET @body = 'New record '+'''' + Convert( varchar,@ConfigSetID_Ins) + ',' + @Name + ',' + Convert( varchar,@Version) + ' , ' +
Convert( varchar,@Timestamp) + ''''+' has been added to '+'CongifSet table in XXXX'
insert logger select @Body, @Subject, @Name, @Version, @Timestamp
/*
EXEC msdb..sp_send_dbmail
@profile_name = 'sqlserverdba',
@recipients = 'venkata@.com',
@subject = @subject,
@body = @body
END
*/
GO
After you run some DML, check the table. This will help you determine what is broken.
April 15, 2013 at 9:33 am
Thanks Steve, i have updated the code as per your suggestion....please see below
create table logger
( Body varchar(500)
, ConfigSetID_Ins varchar(100)
, Subject varchar(104)
, Name varchar(200)
, Version varchar(100)
, [Timestamp] varchar(100)
)
ALTER TRIGGER [dbo].[ConfigTrigger]
ON [dbo].[ConfigSet]
AFTER INSERT--, DELETE, UPDATE
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)
BEGIN
Select @Subject = 'New record has been added in ConfigSet table in XXXXXX';
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
--from DELETED d join ConfigSet C on d.ConfigSetID = C.ConfigSetID
SET @body = 'New record '+'''' + @ConfigSetID_Ins + @Name + @Version + @Timestamp + ''''+' has been added to '+'ConfigSet table in xxxxxx'
insert into logger select @Body, @ConfigSetID_Ins, @Subject, @Name, @Version, @Timestamp
end
Record has been added in logger table but Body column is null which is important for us.....
BodyConfigSetID_InsSubject NameVersionTimestamp
NULL33 New record has been added in ConfigSet table in XXXXXXyouareth1NULL
April 15, 2013 at 10:44 am
you are pretty consistently ignoring the possibility that values might be null.
You've got to bulletproof your code to consider datatypes (converting int to varchar, for example), where the data might not exist at all in the INSERTED or DELETED tables, or where any of your column values are NULL.
what happens when you replace your line with this?
SET @body = 'New record '
+ ''''
+ ISNULL(@ConfigSetID_Ins,'[Missing ConfigSet]')
+ ISNULL(@Name ,'[Missing Name]')
+ ISNULL(@Version,'[Missing Version]')
+ ISNULL(@Timestamp,'[Missing Timestamp]')
+ ''''
+ ' has been added to '+'ConfigSet table in xxxxxx'
Lowell
April 15, 2013 at 1:17 pm
As welll as what Lowell says, you need to distinguish between the three possible causes of entering the trigger: Insert, update, and delete. Also, if you are signalling unexpected events, you shouldn't restrict your trigger to single row operations - after all, it's supposed to tell you when something unexpected happens, and maybe multi-wow inserts/updates/deletes are unexpected?
You could comboine that with what Lowell said and get something like this:-
declare @optype tinyint = 0;
if exists (select * from inserted) set @optype = @optype+1
if exists (select * from deleted) set @optype = @optype+2
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 ;
of @optype = 1 or optype = 3 set @Body = 'New rows:'
if @optype = 1 or @optype = 3
select @Body = @Body + <newline marker> +
ISNULL(@ConfigSetID_Ins,'[Missing ConfigSet]') +','
ISNULL(@Name ,'[Missing Name]')+','
ISNULL(@Version,'[Missing Version]')+','
ISNULL(@Timestamp,'[Missing Timestamp]')+ <newline marker>
from inserted ;
if @optype = 2 or @optype = 3
select @Body = @Body + 'Old Records:' + <newline marker> +
ISNULL(@ConfigSetID_Ins,'[Missing ConfigSet]') +','
ISNULL(@Name ,'[Missing Name]')+','
ISNULL(@Version,'[Missing Version]')+','
ISNULL(@Timestamp,'[Missing Timestamp]')+ <newline marker>
from deleted ;
Tom
April 16, 2013 at 8:03 am
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
April 16, 2013 at 8:31 am
The "inserted" table contains the new values. The "deleted" table contains the old ones. Change that, or add more code if you need both.
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply