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
SSChasing Mays
SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)

Group: General Forum Members
Points: 614 Visits: 1228
Thanks you very much guys...i will give a try with Lowell code and let you know the status...
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88528 Visits: 41130
I guess I'd just put and INSTEAD OF trigger on the table that returned the error "Table is Read Only" and which group of people to contact for modifications and then exit.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Robin35
Robin35
SSChasing Mays
SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)

Group: General Forum Members
Points: 614 Visits: 1228
I'm not able to get this done....here is my table structure...

CREATE TABLE xxxxx (
[ConfigSetID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Version] [smallint] NOT NULL,
[OverrideID] [int] NOT NULL,
[Timestamp] [binary](8) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[xxxxx] ADD DEFAULT ('') FOR [Name]
GO

ALTER TABLE [dbo].[xxxxx] ADD DEFAULT ((0)) FOR [Version]
GO

ALTER TABLE [dbo].[xxxxx] ADD DEFAULT ((0)) FOR [OverrideID]
GO

ALTER TABLE [dbo].[xxxxx] ADD DEFAULT ((0)) FOR [Timestamp]
GO

i need to create a trigger that tracks any changes on this table and send email to team when there is any update.

i'm getting rid of EVENTDATA, trying to implment inserted and deleted tables ...
Please help me on this...

Thanks in advance....
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26540 Visits: 17557
krishnavenkat16 (4/12/2013)
I'm not able to get this done....here is my table structure...



Look at the example Lowell posted. About all you need to do is change it from a delete trigger to an update. Maybe a few minor modifications here and there but what he posted is 99.9% what you need.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Robin35
Robin35
SSChasing Mays
SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)

Group: General Forum Members
Points: 614 Visits: 1228
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

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

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Robin35
Robin35
SSChasing Mays
SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)

Group: General Forum Members
Points: 614 Visits: 1228
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28752 Visits: 39980
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

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

Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64541 Visits: 19118
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.

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
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40446 Visits: 38567
I have to agree with Steve on this one.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
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