DML Triggers

  • Comments posted to this topic are about the item DML Triggers

    Thanks,
    Prashant

  • There are 3 types of DML triggers: CLR Triggers, AFTER Triggers,INSTEAD OF Triggers. Answer given is wrong in the post.

    -------------------------------------------
    Cheers,
    Sur...
    '[font="Tahoma"]The bend in the road is not the end of the road Unless u refuse to take the turn[/font]"

  • That's just what I was going to point out. I even went & checked it in BOL.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Hey Me too..

    This site

    http://msdn2.microsoft.com/en-us/library/ms178134.aspx

    says that A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger can also be a DDL trigger.

    So... please correct the answer...


    Regards,
    Genie Cool

  • Even though a CLR trigger can actually be an After or Instead Of trigger, it is still a unique type and makes the answer to the question 3 instead of 2.

  • Three! Says so right here. Answer choices are incorrect. However, I suppose you could quibble about the CLR trigger thing.

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2e3f74a7-05b6-445a-8156-4aa10232c324.htm

  • Hi guys,

    I think question and answer both are correct. because if you people see the question read it carefully,

    "How many types of DML triggers in SQL are present? (DML - Data Modification Language)"

    The question says that How many types of DML Triggers ok, its not asking how many type of triggers, its asking about DML Triggers,

    So the answer is AFTER and INSTEAD of trigger.

    But tell you the intresting thing that i have also ticked the 2nd option that is

    3 types (DML triggers, DDL triggers and Logon triggers)

    But if u'll read the question carefully then the question and answer both are correct.

    Thanks...:)

    Thanks,
    Prashant

  • In this BOL page, http://msdn2.microsoft.com/en-us/library/ms178134.aspx titled Types of DML Triggers, it states After, Instead Of, and CLR Triggers are types of DML triggers. For triggers are used prior to SQL Server 2005. The only option in the answer that includes all of these is answer number 4.

  • It's definitely 3. BOL (normally) doesn't lie.

  • rlondon (12/12/2007)


    In this BOL page, http://msdn2.microsoft.com/en-us/library/ms178134.aspx titled Types of DML Triggers, it states After, Instead Of, and CLR Triggers are types of DML triggers. For triggers are used prior to SQL Server 2005. The only option in the answer that includes all of these is answer number 4.

    HI,

    Actually in the page CLR also listed as DML triggers but if you see CLR triggers can be used as DML as welll as DDL triggers, and also

    "CLR triggers are dependent to .NET " that is why there is only the pure DML triggers that are AFTER and INSTEAD, CLR comes under both CML and DDL,

    this is what i think.... What you think.,..

    Thanks,
    Prashant

  • To whomever creeated this question, please check BOL, under "Types of DML Triggers" topic.

    It clearly states there are three types of DML triggers. But if you count the FOR trigger, that would be 4.

  • I agree with rlondon. Answer should be 4; FOR is the only option available in earlier versions of Microsoft SQL Server for AFTER triggers and the question did not specifically ask for SQL Server 2005.

  • Prashant Pandey (12/12/2007)


    But tell you the intresting thing that i have also ticked the 2nd option that is

    3 types (DML triggers, DDL triggers and Logon triggers)

    But if u'll read the question carefully then the question and answer both are correct.

    Prashant, didn't you post the question in the first place??

  • I was surprised at the logic:

    "....This link also contain CLR Triggers but they can also be a DDL trigger that is why they cannot be count under the pur DML triggers category..."

    If CLR Triggers can be something else than DML Triggers, it does not mean it is not the valid type for DML Triggers. It just means it is the valid type for other types of triggers too. The question was "How many types for DML Triggers" and it is 3. This is similar of how we have a "priority one" for so many different things and the fact that we have issues of "priority one" , tasks of "priority one", goals of "priority one" etc does not mean that "priority one" is not a valid type for issues for example because it is a valid type for many other categories.

    Regards,Yelena Varsha

  • Yelena Varshal (12/12/2007)


    I was surprised at the logic:

    "....This link also contain CLR Triggers but they can also be a DDL trigger that is why they cannot be count under the pur DML triggers category..."

    If CLR Triggers can be something else than DML Triggers, it does not mean it is not the valid type for DML Triggers. It just means it is the valid type for other types of triggers too. The question was "How many types for DML Triggers" and it is 3. This is similar of how we have a "priority one" for so many different things and the fact that we have issues of "priority one" , tasks of "priority one", goals of "priority one" etc does not mean that "priority one" is not a valid type for issues for example because it is a valid type for many other categories.

    HI,

    ur right... i think i am wrong, But if u see that question is "How many Types of DMLTriggers" so i think if u ask about all versions of sql then commonly there are two. AFTER and INSTEAD and FOR is not a seprate it comes under AFTER,

    CLR Triggers are implemented in SQL 2005. they are not in 7.0 and 2000 and i think AFTER and INSTEAD OF both are there in all versions.

    but if u consider the latest which always prefered .. then question needs some modification and it should be like below.

    How many Types of DML Triggers in the SQL2005??

    and answer will be AFTER, INSTEAD OF and CLR Triggers.

    What you think???

    Thanks,
    Prashant

Viewing 15 posts - 1 through 15 (of 35 total)

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