SQL Server 2005 Trip Wires

Sign in to queue

The Discussion

  • User profile image
    matthester
    This is Fantastic!  Thanks Keith for putting this together.  Trip Wires are great ways to use the triggers.
  • User profile image
    keithcombs
    matthester wrote:
    This is Fantastic!  Thanks Keith for putting this together.  Trip Wires are great ways to use the triggers.


    No problem.  Looks like we have a nice new vehicle for doing some fun demos.  I plan to do stuff related to work and play, so stick around for the hobby stuff.

    Since I didn't get an xbox360 today, it looks like I'm going to have more time on my hands to cry... [C]
  • User profile image
    AIM48
    Isn't Drop Trigger a ddl event. How did it execute without firing the trigger?
  • User profile image
    keithcombs
    AIM48 wrote:
    Isn't Drop Trigger a ddl event. How did it execute without firing the trigger?


    Permissions of course!!! 

    To drop a DML trigger requires ALTER permission on the table or view on which the trigger is defined.

    To drop a DDL trigger defined with server scope (ON ALL SERVER) requires CONTROL SERVER permission in the server. To drop a DDL trigger defined with database scope (ON DATABASE) requires ALTER ANY DATABASE DDL TRIGGER permission in the current database.

    Be careful with making any changes to this behavior.  I don't want to get a call that someone has painted themselves into a corner. [C]

    A similar DDL trigger example is provided with the AdventureWorks sample database. To obtain the example, find the Database Triggers folder in the SQL Server Management Studio Object Explorer, located under the Programmability folder of the AdventureWorks database. Right-click ddlDatabseTriggerLog and select "Script Database Trigger as". By default, DDL trigger ddlDatabseTriggerLog is disabled.

Add Your 2 Cents