Post reply

Policy Based Management Trigger Facet naming convention

  • August 9, 2012 at 5:26 am

    #279357

    Hi Guys

    I'm new to using Policy Based Management in SQL Server 2008 r2.

    I need to create a policy that would stick to the following naming convention for a trigger

    INSERT Trigger naming convention need to start with TRI

    UPDATE Trigger naming convention needs to start with TRU

    Thanks in advanced

    dbadude78

  • August 9, 2012 at 5:32 am

    #1523679

    Would want to use the trigger facet, with name and either insert or update depending on the trigger as the conditions

  • August 9, 2012 at 5:41 am

    #1523685

    Hi

    Thanks for the quick response

    I have selected the the Facet Trigger but not sure have to evaluate the @Name and @Update in the expression builder together.

  • August 9, 2012 at 5:44 am

    #1523687

    @Update or @Insert should be = True and @Name should be LIKE 'TRI%' or 'TRU%' depending on the trigger. They will need to be evaluated in the same condition

    @Update = True

    AND @Name LIKE 'TRU%'

  • August 9, 2012 at 6:02 am

    #1523698

    Thanks for that, it worked perfectly

    🙂

  • August 9, 2012 at 6:05 am

    #1523701

    Not a problem, glad all is working for you.

    I found PBM to be a bit tricky first time I started using it, but you soon learn the ways of doing it.

  • August 9, 2012 at 6:18 am

    #1523715

    Beware that is a bit tricky policy. According to the business requirement you need to handle the case when a trigger is defined both for INSERT and UPDATE simultaneously.

  • August 9, 2012 at 7:33 am

    #1523771

    Hello again

    When I try to enable the New Trigger policy I get the following error

    'Evaluation Mode 'On demand' deos not support Enabled = 'True'

    The setup is as follows they are on separate lines

    AndOr Field Operator Value

    @Name Like 'TRI_%'

    AND @INSERT = True

    You said on your post, they need to be evaluated in the same condition. Not sure how to do that

    The only options in the Evaluation Mode are On demand and On schedule, the On change prevent is not available.

    thanks

  • August 9, 2012 at 7:48 am

    #1523783

    If you have two lines in the condition for the facet, one with @insert or @update and the other with @name then it is setup right.

    The policy for that type of facet and condition can only evaluate as on demand or on schedule, you must have on demand set as the evaluation mode for the policy to which you cannot enable the policy it must be run manually.

    Change the evaluation mode to on schedule and set a schedule which will allow you to enable it.

    You wont be able to create an on change prevent for this facet and condition.

    SQL PBM Blog - Facets

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply