Click here to monitor SSC
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
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10731 Visits: 12019
krishnavenkat16 (4/16/2013)
....but the requester is looking for old item i mean records before the update...

You commented the code that does that out:
if @optype = 2 --or @optype = 3


the or @optype = 3 is what puts in the old (pre-update) values in the update case; the code I suggested gives both the pre-update values and the post-update values, so that you can see both what has been changed and what it has been changed to.

The delete case having a null body was a silly omission in what I suggested. the declaration of body needs to be changed to

DECLARE @Body varchar(500); set @Body = '';


so that it isn't null even if there are no new rows.

Tom

Robin35
Robin35
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 1069
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[s] inserted into ConfigSet table in XXXXXX'
when 2 then 'Row[s] deleted from ConfigSet table in XXXXXXX'
when 3 then 'Row[s] modified in ConfigSet table in XXXXXX'
else 'Nothing Happened'
end ;

SELECT @ConfigSetID_Ins = i.ConfigSetID, @Name = i.Name, @Version = [i].[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
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10731 Visits: 12019
krishnavenkat16 (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....

Actually with that code you are getting the new values for updates and not the old values. Maybe that's not a problem?

Tom

Robin35
Robin35
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 1069
Hey Tom,

Here what i'm getting....
for insert...new records are logged - with this requester can know what record is inserted
for update....old records are logged - with this Requester will know what has been updated
for delete ....deleted records are logged - with this Requester will know what has been deleted

fyi, i have removed the comment on @optype = 3

i guess this result is fine....please let me know if i need to update the code...
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10731 Visits: 12019
krishnavenkat16 (4/16/2013)
Hey Tom,

Here what i'm getting....
for insert...new records are logged - with this requester can know what record is inserted
for update....old records are logged - with this Requester will know what has been updated
for delete ....deleted records are logged - with this Requester will know what has been deleted

fyi, i have removed the comment on @optype = 3

i guess this result is fine....please let me know if i need to update the code...

Yes, removing that comnment fixes it. I don't see any other problem. Glad you've got something that works - it took a while for us to help you there.

Tom

Robin35
Robin35
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 1069
Yes, thank you very much ..
dba.sql29
dba.sql29
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 1205
Thank you for this DML trigger.

When I am running this trigger I am getting syntax error is there any thing I need to change in that trigger ?
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10731 Visits: 12019
dba.sql29 (4/25/2014)
Thank you for this DML trigger.

When I am running this trigger I am getting syntax error is there any thing I need to change in that trigger ?


There shouldn't be a syntax error as it worked for Robin35. Have you changed anything?
Anyway, if you tell us what the error message is maybe we can help.

Tom

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45147 Visits: 39923
Ah, sorry. I was doing a little test and hit post instead of prview.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45147 Visits: 39923
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[s] inserted into ConfigSet table in XXXXXX'
when 2 then 'Row[s] deleted from ConfigSet table in XXXXXXX'
when 3 then 'Row[s] modified in ConfigSet table in XXXXXX'
else 'Nothing Happened'
end ;

SELECT @ConfigSetID_Ins = i.ConfigSetID, @Name = i.Name, @Version = [i].[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






Actually, while I'm here...

If I'm reading the code correctly, the code has a bit of a flaw in it that you might want to consider. If someone inserts, updates, or deletes more than 1 row in the same insert, update, or delete, what do you want to be contained in the email? I suspect it will be that you want more than 1 row?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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