Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Create DML trigger for single table in SQL 2008 and send email alert to group Expand / Collapse
Author
Message
Posted Friday, April 12, 2013 3:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 92, Visits: 738
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.

Post #1441936
Posted Friday, April 12, 2013 3:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:22 PM
Points: 12,889, Visits: 31,839

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1441937
Posted Saturday, April 13, 2013 3:53 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 8,571, Visits: 9,076
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
Post #1442024
Posted Sunday, April 14, 2013 8:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 92, Visits: 738
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
Post #1442154
Posted Monday, April 15, 2013 8:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:10 PM
Points: 33,095, Visits: 15,202
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
Post #1442337
Posted Monday, April 15, 2013 9:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 92, Visits: 738
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
Post #1442366
Posted Monday, April 15, 2013 10:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:22 PM
Points: 12,889, Visits: 31,839
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1442408
Posted Monday, April 15, 2013 1:17 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 8,571, Visits: 9,076
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
Post #1442469
Posted Tuesday, April 16, 2013 8:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 92, Visits: 738
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




Post #1442755
Posted Tuesday, April 16, 2013 8:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:10 PM
Points: 33,095, Visits: 15,202
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
Post #1442770
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse