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 ««1234»»»

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

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 7:36 AM
Points: 80, Visits: 662
Thanks you very much guys...i will give a try with Lowell code and let you know the status...
Post #1441493
Posted Thursday, April 11, 2013 5:05 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 9:45 PM
Points: 36,013, Visits: 30,300
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1441524
Posted Friday, April 12, 2013 11:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 7:36 AM
Points: 80, Visits: 662
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....
Post #1441810
Posted Friday, April 12, 2013 11:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 12,016, Visits: 11,046
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 Moden's 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)
Post #1441817
Posted Friday, April 12, 2013 12:10 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 7:36 AM
Points: 80, Visits: 662
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
Post #1441831
Posted Friday, April 12, 2013 12:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 12,016, Visits: 11,046
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 Moden's 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)
Post #1441832
Posted Friday, April 12, 2013 12:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 7:36 AM
Points: 80, Visits: 662
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
Post #1441840
Posted Friday, April 12, 2013 12:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

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

--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 #1441841
Posted Friday, April 12, 2013 1:32 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 4:29 PM
Points: 32,819, Visits: 14,965
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
Post #1441887
Posted Friday, April 12, 2013 1:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:44 PM
Points: 22,525, Visits: 30,290
I have to agree with Steve on this one.


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)
Post #1441905
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse