April 17, 2013 at 3:58 pm
I have set up DDL events notifications using service broker. I now am trying to set up a trigger on a certain table to fire an email on a new event.
I have one trigger shredding the xml and inserting into certain tables.
I then tried to create a new triggeron a specific table but when I make a change the new trigger is somehow disabling service broker queue. I'm new to tql trigger and service broker. I have gathered the information to build the event data from multiple places on the web. It works, just trying to get the email to send out on change with certain criteria is failing on me. I can add the sql if needed, maybe its the trigger.
Thanks in advance
April 18, 2013 at 7:55 am
Please provide scripts for all tables/triggers involved. I am also a bit unclear - are you trying to actually SEND the email from a trigger or queue up a SSB message that will be used to asynchronously send the email?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 18, 2013 at 9:18 am
I havethe table and the existing trigger that works. I originally had a server level trigger set up that works good but seemed to have some issues when our environment performed certain task in the database. So i had to figure out a different way to track the ddl events. I found useful info on this site and another. As for now the tracking works with the logging i just need a way to send the email on change as well like this example. I tried creating a new trigger on a table that has this information but when its enabled it disables service broker no errors im sure there is a conflict somewhere. Help is approciated thanks
This is how it works now
Database level event 104 has occurred in database [SChemaChange.[DBA]:
Schema change event type: CREATE_TABLE
Object: [dbo].[Table_2]
Changed by:
Changed datetime: Apr 16 2013 9:59AM
Please verify that this is an Authorized Change.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply