SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DML Triggers


DML Triggers

Author
Message
Sur-484303
Sur-484303
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 49
The question is: "how many types of DML triggers in present?" So the answer should be : 3 types of DML triggers: CLR Triggers, AFTER Triggers,INSTEAD OF Triggers.

Cheers,
Sur...

-------------------------------------------
Cheers,
Sur...
'The bend in the road is not the end of the road Unless u refuse to take the turn"

Craig@Work
Craig@Work
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 326
I'm afraid the answer is wrong .... saying that the CLR trigger isn't a DML trigger because they can also be DDL triggers is just daft

Don't know how poster will cope with quantam computing ... where qubits can be on, off or both Smile does that mean a qubit isn't a bit? or it can never be on or off because it can be both?

Seems question is an attempt to 'prove' how clever poster is, and succeeds in demonstrating opposite BigGrin
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10854 Visits: 11971
Hi all,

I'll have to disagree with the many objections and agree with the original question (now that is uncommon for me!). Though the explanation is confusing, misleading, and maybe even wrong, the answer is, in fact, correct.


The explanation that CLR doesn't count as a seperate category because they can also be used as DDL triggers is of course utter ****. No arguing there. However, that doesn't make it a seperate category. It's just a different language to code a trigger in. So we have two categories: AFTER triggers (which may also be defined by using the keyword FOR instead of AFTER), which can be coded in either T-SQL or in .Net, and INSTEAD OF triggers, which also can be coded in either T-SQL or in .Net.

If you want to consider CLR triggers a seperate category, fine - but then the total immediately bumps to four, because you then still have to distinguish CLR INSTEAD OF triggers from CLR AFTER triggers. There was no answer option including these two types of triggers, so it was clear that the "type" did not refer to the language.

Everyone arguing that "CLR triggers" is in itself a single type of trigger is just wrong. So there, I've said it - let the flaming commence! Wink


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Andrew Fitzgerald
Andrew Fitzgerald
SSC Eights!
SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)

Group: General Forum Members
Points: 844 Visits: 12
I'll post my two cents worth.

The question asks "How many types of DML triggers in SQL are present? (DML - Data Modification Language)" - it doesn't ask "How many types of pure DML triggers in SQL are present? (DML - Data Modification Language)".

Since CLR is present in the DML language the correct answer based on the way the question is written is three.

The answer given makes a qualification that is not in the question - That is the worst kind of trick question I can imagine.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39007 Visits: 38508
I don't know, but I got the answer (per QoD) correct because it makes sense to me. There are two types of DML triggers: AFTER and INSTEAD OF. These can be written in TSQL or a CLR language. Just because BOL lists CLR triggers separately doesn't mean it is correct. Please, Microsoft doesn't always know best.

Cool

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10854 Visits: 11971
Andrew Fitzgerald (12/13/2007)Since CLR is present in the DML language the correct answer based on the way the question is written is three.


Hi Andrew -- and everyone who has posted a similar thing.

Please explain to me why you would divide DML triggers written in T-SQL into two categories (INSTEAD OF and AFTER), while at the same time lumping all DML triggers written in CLR into a single category? If a T-SQL INSTEAD OF trigger is different from a T-SQL AFTER trigger, then how can a CLR INSTEAD OF trigger not be different from a CLR AFTER trigger?

There is no definition given for "type" in the question, so we have some leeway to create our own definitions. I can live with the answer "Two: CLR and T-SQL" (defining language as type). I can also live with the answer "Two: INSTEAD OF and AFTER" (as the question's author intended). And I'll also accept "Four: T-SQL INSTEAD OF, T-SQL AFTER, CLR INSTEAD OF, and CLR AFTER" as a legitimate answer. But three? No way!


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Andrew Fitzgerald
Andrew Fitzgerald
SSC Eights!
SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)

Group: General Forum Members
Points: 844 Visits: 12
MS On-line help for SQL lists three Types of DML triggers: After, Instead of and CLR.

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

The only one in dispute is CLR which according to the authors of the question also exists in other Features and thus is not "pure". It may be semantics but the three that MS Lists trumps the answer given in my book.
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10854 Visits: 11971
Andrew Fitzgerald (12/13/2007)
MS On-line help for SQL lists three Types of DML triggers: After, Instead of and CLR.


Tempted as I now am to submit a documentation bug report, I can't deny that this is indeed exactly what Books Online says. I disagree with it (but that's not new, I disagree with lots of stuff in BOL), but it is official so I guess that those who answered "three (after, instead, clr)" should be awarded a point.

And maybe the question needs to be retracted...


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Craig@Work
Craig@Work
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 326
.... 4 surely - this included AFTER and INSTEAD OF and CLR, so we had to select this even though it also included FOR (even though this isn't really a type on its own) ...retract the question and revoke all points Smile
Johann Steenkamp-328619
Johann Steenkamp-328619
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1492 Visits: 139
The answer should be 3, After, Instead Of and CLR
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search