Re: Trigger Code

good observations, some comments in line

;-{ mcs

"Jim McMahon" <jp_mcmahon_at_hotmail.com> wrote in message news:405d8c29.1209391041_at_news.charter.net...
| "Redd" <javatek?@usa.com> wrote:
|
| >
| >
| >--
| >javatek?@usa.com
| >
| >remove the ? to reply.
| >"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
| >news:PfWdnc-muMypasHdRVn-gQ_at_comcast.com...
| >> | Quantity Integer
| >> | OldQuantity Integer
| >> | OnOrder Char
| >> |
| >> |
| >> | Does anyone know how to write the trigger that on update
| >> | if Quantity is equal to or less than OldQuantity write the
| >> | Character "T" to the OnOrder field.
| >> |
| >> |
| >> |
| >> |
| >>
| >> use a 'before update ... for each row' trigger with a 'when' condition
| >that
| >> compares the oldquantity and new quantity, and assign the value 'T' to
| >> onorder
| >What I would like is to see what that actual code segment would look
like.
| >We are going to use this as a small test to see if any of the DBAs to be
| >interviewed can write triggers. I am not a programmer.
| >
| >
|
| It's really a big mistake to have non-technical people evaluate
| technical skills, especially with a syntax driven question. I'm not a
| DBA (I'm a programmer), but I've written triggers for different
| databases, and with minimal effort can figure out how to do so for any
| database I may be asked to work with. It's not bragging, it's just
| not that hard.
|
| Just in case you don't know what I mean by the phrase a "syntax driven
| question", I'll use Mark's sample trigger from his reply as an
| example.
|
| --- begin extract from Mark's message ---
| create or replace trigger sample_trigger
| before insert or update on t1
| for each row
| when (new.quantity > old.quantity)
| begin
| :new.onorder := 'T';
| end;
| --- end extract from Mark's message ---
|
|
| One example of the "syntax" in this sample are the dot "." between the
| namespace and field name (eg. "new.quantity" and "old.quantity")
| [which, by the way, I think should be ":new.quantity" and
| ":old.quantity" instead].

this is one of those little gotcha's -- in the WHEN clause, you don't use the colon -- but as you note, i too wouldn't expect a dba to have this syntax anamoly memorized, i'd except the dba to be able to diagnose the compilation error

(btw -- to fit the sample's data structure, i should have compared new.quantity and new.oldquantity)

|Other examples of syntax are the single
| quotes around the character value ('T') to be changed and the
| semi-colons (;) which end the statements.
|
| The syntax and other nuances of any particular environment
| (programming language, database, IDE, or other tool) are usually
| contained in the documentation, so many professionals don't memorize
| them. Rather, they are familiar with concepts that most environments
| support (eg. triggers for databases) and know how to quickly figure
| out (look up, RTFM) any environment specific details.
|
| While syntax driven questions and other performance based interview
| techniques can be useful if you are looking for someone with specific
| experience (say, an Oracle DBA, but not other DBA flavors), I think
| you increase the risk of overlooking qualified applicants when
| non-technical people are the only ones evaluating technical skill.
|
|

yep -- i think both of us would have been filtered out, sinced i got the syntax wrong in my first build of the sample, and you forgot the when clause anomoly -- and we are both _highly_ qualified, no? ;-)

|
|
| Being ordinary and nothing special is a full-time job.
| jp_mcmahon_at_hotmail.com (Jim McMahon in real life)
Received on Sun Mar 21 2004 - 07:27:27 CST