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

DDL Trigger on Table Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 3:58 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 79, Visits: 619
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
Post #1443523
Posted Thursday, April 18, 2013 7:55 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 4,320, Visits: 6,113
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 at GMail
Post #1443879
Posted Thursday, April 18, 2013 9:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 79, Visits: 619
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.
Post #1443941
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse