Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DDL Trigger on Table


DDL Trigger on Table

Author
Message
D-SQL
D-SQL
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 754
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
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5935 Visits: 8298
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
D-SQL
D-SQL
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

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