How to Test Triggers ?

  • Hi Experts,

    I have some questions regarding SQL server 2008. Below situations are from DB testing arena .

    1) How to check a trigger is fired or not, while doing database testing?

    2) Can you delete a parent table record if there is a child table record exits ? How ?

    Any additional suggestion / idea on "How to Test Triggers" would be a great help.

    Thanks in Advance.

  • For triggers read this.

    http://msdn.microsoft.com/en-us/library/ms189799.aspx

    2) Can you delete a parent table record if there is a child table record exits ? How ?

    Suggest you use google to search for "Cascade delete"

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • adc22 (4/29/2012)


    Hi Experts,

    I have some questions regarding SQL server 2008. Below situations are from DB testing arena .

    1) How to check a trigger is fired or not, while doing database testing?

    2) Can you delete a parent table record if there is a child table record exits ? How ?

    Any additional suggestion / idea on "How to Test Triggers" would be a great help.

    Thanks in Advance.

    Well, you could run a trace via Profiler to test if the trigger is fired, or you could read through the code and look at what the trigger does and directly check the trigger changes, which is usually what you want to do when testing.

    For parent/child deletes, this depends on what Referential Integrity is setup, in particular what settings are on/off for the Foreign Keys, assuming there even are any. However, yes, depending on the settings, it's very easy to remove a parent and orphan children, thus why FKs are so popular. DELETE FROM tblParent WHERE ID=@number is a pretty easy way to do it, depending on the schema. Without the schema it's hard to describe what methods are available though.

    As to other tests, it depends on what triggers are existing, are they instead of vs. after triggers, and what their intent are. Can you drop the schema here? It'd help us help you figure out what way to approach this. However, if you're a tester, don't you have a test-pattern already established by your QA Lead?


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Thanks bitbucket-25253 & Craig 🙂

    @Craig: Actually a new Project is on the way. So just preparing in advance. 🙂

    One more related question .. can I assume Parent a <strong> entity and the child a <weak> entity ?

  • adc22 (4/29/2012)


    Thanks bitbucket-25253 & Craig 🙂

    @Craig: Actually a new Project is on the way. So just preparing in advance. 🙂

    One more related question .. can I assume Parent a <strong> entity and the child a <weak> entity ?

    Not sure where that terminology comes from. It's data, it's dependent on each other, it's all strong in my mind but that's really not understanding what definition you're trying to apply here.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • I see two very different questions here...

    adc22 (4/29/2012)


    1) How to check a trigger is fired or not, while doing database testing?

    Welcome to the wonderful world of testing and quality assurance.

    Do you remember how a process looks like?

    INPUT ===> Process ===> OUTPUT

    ...whoever designs the INPUT has to know what the expected OUTPUT of the process is then, just check what happens when you feed your test INPUT to the target process.

    adc22 (4/29/2012)


    2) Can you delete a parent table record if there is a child table record exits ? How ?

    Assuming there is an active FK constraint you cannot - that's what Referential Integritiy constraints are supposed to enforce, among other stuff.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • adc22 (4/29/2012)


    1) How to check a trigger is fired or not, while doing database testing?

    Same way as when testing any software of old... Add a PRINT or RAISERROR statement to the trigger while testing. 😉

    2) Can you delete a parent table record if there is a child table record exits ? How ?

    By definition, you shouldn't be able to.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (4/29/2012)


    adc22 (4/29/2012)


    1) How to check a trigger is fired or not, while doing database testing?

    Same way as when testing any software of old... Add a PRINT or RAISERROR statement to the trigger while testing. 😉

    I know this is well over 2 years old, but I'm sure my question is valid for others. Where does this PRINT statement show when you fire off your trigger?

  • John Waclawski (10/2/2014)


    Jeff Moden (4/29/2012)


    adc22 (4/29/2012)


    1) How to check a trigger is fired or not, while doing database testing?

    Same way as when testing any software of old... Add a PRINT or RAISERROR statement to the trigger while testing. 😉

    I know this is well over 2 years old, but I'm sure my question is valid for others. Where does this PRINT statement show when you fire off your trigger?

    If you're executing your code from SSMS, it prints in the results pane on the Messages tab.

  • Ed Wagner (10/2/2014)


    John Waclawski (10/2/2014)


    Jeff Moden (4/29/2012)


    adc22 (4/29/2012)


    1) How to check a trigger is fired or not, while doing database testing?

    Same way as when testing any software of old... Add a PRINT or RAISERROR statement to the trigger while testing. 😉

    I know this is well over 2 years old, but I'm sure my question is valid for others. Where does this PRINT statement show when you fire off your trigger?

    If you're executing your code from SSMS, it prints in the results pane on the Messages tab.

    Well that makes too much sense! 😉

    Actually it's an application my company writes and when I change a radio button, it makes changes to a certain table & we want to keep track of the changes. I never thought to update that table through SSMS but will do so & test it out.

    Thank you!!

  • John Waclawski (10/2/2014)


    Ed Wagner (10/2/2014)


    John Waclawski (10/2/2014)


    Jeff Moden (4/29/2012)


    adc22 (4/29/2012)


    1) How to check a trigger is fired or not, while doing database testing?

    Same way as when testing any software of old... Add a PRINT or RAISERROR statement to the trigger while testing. 😉

    I know this is well over 2 years old, but I'm sure my question is valid for others. Where does this PRINT statement show when you fire off your trigger?

    If you're executing your code from SSMS, it prints in the results pane on the Messages tab.

    Well that makes too much sense! 😉

    Actually it's an application my company writes and when I change a radio button, it makes changes to a certain table & we want to keep track of the changes. I never thought to update that table through SSMS but will do so & test it out.

    Thank you!!

    No problem. Glad I could help.

Viewing 11 posts - 1 through 10 (of 10 total)

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