SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create DML trigger for single table in SQL 2008 and send email alert to group


Create DML trigger for single table in SQL 2008 and send email alert to group

Author
Message
Robin35
Robin35
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1221
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.
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27964 Visits: 39922

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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14194 Visits: 12197
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

Robin35
Robin35
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1221
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61655 Visits: 19099
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Robin35
Robin35
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1221
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.....

Body ConfigSetID_Ins Subject Name Version Timestamp
NULL 33 New record has been added in ConfigSet table in XXXXXX youareth 1 NULL
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27964 Visits: 39922
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14194 Visits: 12197
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[s] inserted into ConfigSet table in XXXXXX'
when 2 then 'Row[s] deleted from ConfigSet table in XXXXXX'
when 3 then 'Row[s] 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

Robin35
Robin35
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1221
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[s] inserted into ConfigSet table in XXXXXX'
when 2 then 'Row[s] deleted from ConfigSet table in XXXXXX'
when 3 then 'Row[s] 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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61655 Visits: 19099
The "inserted" table contains the new values. The "deleted" table contains the old ones. Change that, or add more code if you need both.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search