DML Triggers

Question

DML Triggers

Prashant Pandey

SSChasing Mays

Points: 657
More actions
December 11, 2007 at 10:34 pm

#181238

Comments posted to this topic are about the item DML Triggers
Thanks,
Prashant

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

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

Sur-484303 SSC-Addicted Points: 491 More actions · Answer 1

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]"

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Genie Cool SSC-Addicted Points: 478 More actions · Answer 3

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

Josh Turner-440822 SSCrazy Points: 2048 More actions · Answer 4

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.

Wallace Wood Mr or Mrs. 500 Points: 513 More actions · Answer 5

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

Prashant Pandey SSChasing Mays Points: 657 More actions · Answer 6

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

rlondon SSCarpal Tunnel Points: 4653 More actions · Answer 7

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.

Vic Kirkpatrick-173212 SSCommitted Points: 1831 More actions · Answer 8

Vic Kirkpatrick-173212

SSCommitted

Points: 1831

December 12, 2007 at 9:02 am

#759446

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

Prashant Pandey SSChasing Mays Points: 657 More actions · Answer 9

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

mojo-168709 SSCommitted Points: 1825 More actions · Answer 10

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.

Kangana Beri SSCrazy Points: 2184 More actions · Answer 11

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.

Kangana Beri SSCrazy Points: 2184 More actions · Answer 12

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??

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 13

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

Prashant Pandey SSChasing Mays Points: 657 More actions · Answer 14

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