WHEN NEW.employee_id = OLD.employee_idThis interpretation of "code" to not include parts of the CREATE TRIGGER statement and the statement that I reference only the "keywords" and not the values seem to me to be parsing words a bit too closely; some measure of reasonable interpretation is required and these objections both fail on that count. I do not feel that any re-scoring is required for these objections, though I may consider making some changes to the quiz text to the possibility of mis-interpretation in the future. 2. A player wrote: "There is a choice in the today quiz starting with "The trigger fired by the DELETE statement ..." - it does not state that the trigger is a row-level one, so I did not mark it as valid. Is it not mentioned intentionally? BTW, the similar statement regarding triggers fired by INSERT explicitly states the the trigger is row-level one." To which I reply: If the choice that you point out was a "stand alone" statement, then your concern is valid and you would be right to mark it as incorrect. It is, however, a choice for the following question: "Which of the following statements correctly describe how you can reference the new and old column values of the current row affected by the triggering statement of a row-level database trigger? " so clearly the context is "row-level" and I do not believe that any change is needed to the scoring. I will, however, add "row-level" to that choice so that in the future others do not experience the same problem. Cheers, Steven Feuerstein
The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.
11 November 2010
Questions regarding NEW and OLD quiz of 10 November (1643)
The 10 November quiz asked:
"Which of the following statements correctly describe how you can reference the new and old column values of the current row affected by the triggering statement of a row-level database trigger?"
Several players wrote to us with the following concerns:
1. We scored the following as incorrect, because if you use a WHEN clause for your trigger, any references to NEW and OLD within this part of the trigger cannot have a ":" prefix.
"A colon (:) must precede the OLD and NEW qualifiers wherever you reference the old and new column values in the code to define a row-level trigger."
Several players objected to this, stating the "the code" to define a trigger only refers to the part of the trigger that starts with the DECLARE or BEGIN section of the trigger definition. I do not agree. I think that "the code to define a trigger" refers reasonably to all the statements that you write to define the trigger.
Another person objected to this scoring as follows:
"But in the WHEN clause you are not referencing "column values" as you write in the answer. You are just merely defining the keywords. So "wherever you reference the old and new column values" you do need to specify a colon. And therefore I feel that this answer is correct."
How is it that I am not referencing column values when I write:
Subscribe to:
Post Comments (Atom)
You are right, Steven.
ReplyDeleteI was mixing up the WHEN clause and the REFERENCING clause. The WHEN clause does reference column values.
Regards,
Rob.
> "I think that "the code to define a trigger" refers reasonably to all the statements that you write to define the trigger."
ReplyDelete> "I may consider making some changes to the quiz text"
But only 12% of same-thinkers show that at least rephrasing is REQUIRED.
"The trigger definition" means what you mean.
Vitaliy, "12% got it right" is not the same as "12% of same-thinkers." Some portion of the 88% that got it wrong may not have known the syntax required when referencing columns in the WHEN clause or may have responded quickly without considering all of the places where NEW and OLD references might appear. I nearly missed this one because I rarely use the WHEN clause with triggers and thus didn't immediately consider its syntax.
ReplyDeleteI agree with Steven that "the code to define a row-level trigger" definitely includes every statement required to define the trigger. One cannot define a trigger without the CREATE TRIGGER statement and each subordinate clause contributes to the trigger's definition. While it is not uncommon use "trigger's code" to reference just the imperative portions in the context of execution, the context in this case was explicitly declared to be definition.
Hi Steven,
ReplyDeleteas for the "standalone" argument would hold if both INSERT and DELETE choices did not include "row-level". Otherwise the difference in wording strongly hints on a difference in an interpretation. From my point of view this choice should be voided.
Kind regards,
Oleksandr
Well, the SQL "CREATE TRIGGER" statement and the PL/SQL code block are two separate things, which is why you don't need colons in the WHEN clause. I suppose it's all code in the end, but what with this being the PL/SQL Challenge I assumed you meant the trigger code proper. The documentation hints at this in passing, kind of, when it says "The call_procedure_statement lets you call a stored procedure rather than specifying the trigger code inline as a PL/SQL block", not to mention the fact that "CREATE TRIGGER" doesn't appear in the PL/SQL Reference.
ReplyDeletejhall62, I could say "12% of the luck-guesser". But I gave them a credit :)
ReplyDeleteMy point is just rephrasing. Also percentile is very strange.
But you and others could try to say and to hear youself on
"the code to define the table" (I never think about create table as a CODE :) )
"the code to define the package body"
"the code to alter the table"
and so on ...
Unusually? For me - yes. I've read somewhere in the Oracle docs that all of they are usually called SQL statements; but sometimes documentation lies :)
I fully agree to william-robertson. Also a little bit above that in the documentation is written "Specify the trigger condition, which is a SQL condition that must be satisfied for the database to fire the trigger" and in the restrictions is written "You cannot invoke PL/SQL functions or methods in the trigger condition". And this is a "PL/SQL" challange.
ReplyDeleteI don't agree with the idea that the term "code" should be limited only to the executable block of a trigger definition.
ReplyDeleteThe term "code" is very general, and refers to any sequence of characters that can be interpreted by a compiler (or interpreter). That's why we normally qualify it with terms like "procedural code" vs. "declarative code".
I considered this option "wrong" because I consider the code for a trigger to be everything from the CREATE TRIGGER (inclusive) to the terminator character, therefore the WHEN clause is included.
I do sympathise with those who marked it "correct" because I've been there before - second-guessing my and the quiz author's meaning of words :)
Cheers for the sympathy Jeff, I was torn which way to click on this one because I knew not everywhere needed the colon, but like you said, I just had to second guess. Oh well! I thought there might be a bit of reaction to this one.
ReplyDeleteI agree with your "code" comment, it's certainly not a paragraph/novel/essay/documentation... it's code :-)
Steven's done well this week to get us thinking.
I had a concern with a third option. One of the correct answers to this quiz was "You cannot work with NEW and OLD in the aggregate, that is, as a record. You can only reference individual fields of these pseudo-records, matching the names of the columns." While that is strictly true in the case of relational tables, you can work with :NEW and :OLD in the aggregate (though not as a record) in the case of object tables. In an object table, :NEW.object_value is a full-blown instance of the object type and can be passed around just like any other object (see example below).
ReplyDeleteHad the question not used the phrase "as a record", I believe this answer would be unambiguously incorrect. As it stands, the "as a record" clause seems to disqualify the object table exception however the followup comment about only being able to reference individual fields makes the choice murkier.
create type plch_part_obj is object (
part_num number,
part_desc varchar2(100) );
/
create procedure plch_print_part(
p_part plch_part_obj )
as
begin
dbms_output.put_line( p_part.part_desc );
end;
create or replace trigger trg_plch_parts
before insert on plch_parts
for each row
begin
plch_print_part( :new.object_value );
end;
1* insert into plch_parts values( plch_part_obj( 1, 'Tractor' ) )
SQL> /
Tractor
1 row created.
I suspect the "oddly enough" comment in the answer explanation gives away that Steven sees it as all one thing, with an arbitrary syntactical quirk for one of the clauses.
ReplyDeleteIf the question had been:
"A colon (:) must precede the OLD and NEW qualifiers wherever you reference the old and new column values to define a row-level trigger"
I would have immediately said that was false. The fact that it took the trouble to refer to the code specifically suggested that it was referring to the PL/SQL code section.
William, I like your phrasing better. I should have chosen something more general, and will change the question to do that. I still feel, though, that "code" is a sufficiently general reference and will not be changing the scoring.
ReplyDeleteSF