Merge triggers

Had a quick puzzler at work today that I thought I'd share... and the link to Ask Tom that solved it (before I could be bothered to delve into the test case myself).

Q: Which statement level triggers fires on a MERGE statement?

A: Since it is possible for either inserts or updates to occur during the statement, and since statement level triggers always fire even when no rows are processed, both the INSERT and UPDATE triggers fire every time, regardless of whether any inserts or updates occur.

Obvious when you think about it. Trouble is, how many our YOUR statement level triggers would work properly if both sets fired at the same time? There's no reason why they shouldn't, if you code for the possibility....

Tom (and Mikito and Kevin and others) explains here.

World Cup Trivia

Well, I'm finally back from my honeymoon, and with the wedding out of the way I may finally get some time to write blog entries again. But wait, hang on, it seems like there's a World Cup (footy that is) to take up almost all of my evenings. Ah well, no blogging just yet then!

Still, there's no matches on at the moment (not until Friday anyway) and so to quench my need for football I decided to go on a trivia hunt. And thus was born Honest Bob's World Cup Trivia facts... I can't guarantee the accuracy of any of these facts as all were researched on the internet ;-)

Of the 17 events (prior to Germany 2006), the hosts have won 6 times, with only Sweden being a losing host finalist (1958, losing to Brazil).

Argentina and Brazil are the only countries to win outside of their own continent. Brazil have managed to win on every continent the competition has been played:

• Asia: South Korea / Japan (2002)


• North America: USA (1994), Mexico (1970)


• South America: Chile (1962)


• Europe: Sweden (1958)

Not only that, but Brazil have managed to qualify for every world cup tournament.

However, they are the only team that have won the competition to have NOT won it as hosts.

Although a German team have won the competition 3 times, they've never managed it as a combined Germany, only ever as West Germany.

In total only 7 teams have won the competition out of 207 countries who have competed for qualification, and 78 who have made it to the world cup proper. The 7 winners are:

• Brazil (5 times)


• Italy (3 times)


• West Germany (3 times)


• Uruguay (2 times)


• Argentina (2 times)


• England (once)


• France (once)

The competition has been utterly dominated by European and South American teams, with only two teams outside of the two continents having made it to the semi-final stages. USA (1994) and South Korea (2002) both being hosts when they managed it.
The fastest goal in a world cup match was scored by Hakan Sukur (Turkey), 11 seconds after kick off against South Korea in the 2002 tournament.

However, that time is beaten when you also take into account the qualifying matches. David Gualtieri (San Marino) scored after 8 seconds against England in their ill-fated qualification attempt for the 1994 Finals. England needed to win by 7 clear goals and have Holland lose their match against Poland. England only managed to win 7-1, though it mattered little as Holland eventually won their game 3-1.

In the whole of that qualification group (10 games) San Marino managed to score only one other goal and conceded 46.

The 1950 World Cup was not decided by a final. Rather it was a league contested by 4 teams, with the match between Uruguay and Brazil (2-1) being the decisive match and therefore generally regarded as 'the final'.

The only person to have played both World Cup Football and World Cup Cricket is Viv Richards - West Indies at cricket (obviously) and for Antigua in their 1974 World Cup Qualifying matches, which ultimately ended in failure.

Best Practice?

Yeah yeah, it's been very quiet on the Bob front recently... I have an excuse though, it's only a week and a half to my wedding, so I've been a busy busy boy. Plus I've got the task of completely rewriting our development manual, so that's taking all of my creative juices. I'm running on empty.

But, when you get sent a link like this, you just have to share:


If you're anything like me, you'll absolutely hate the phrase 'Best Practice'. I cringe every time I hear it. It suggests to me that the speaker has stopped learning... "This is the best practice there could possibly be, so there's no point in trying to improve it". In addition it has the connotation that "This is the best practice for all situations, whatever it may be". Sorry, but that just doesn't work.

So anyway, a site that calls itself Fairly Good Practices was always going to pique my interest...

In my spare time I contribute to an OS CMS...

Whenever I see a CV cross my desk containing the words 'Open Source Content Management System', I shiver. Visibly.

So what was I supposed to do with the news that my manager had decided to write one?

Well, it turns out that I needed to take a look at the home page, then take a look at then code, and finally decide that it's not actually that bad.

Obviously, the last thing the world needs is yet another Wiki, but as it's actually very very lightweight, looks ludicrously easy to set up, and is very well structured, then why not make space for just one more?

Obviously, he needs a slap for not writing unit tests, but the plug in authentication and storage classes are spot on.

In fact, I'd like to present it as an example of how PHP code can look when its not produced by an idiot. In addition, the CSS driven layout is a great example of content and presentation separation. All in all it's starting from a very nice position. Hopfeully it'll continue in the same way.

There's the odd little bit of refactoring to do, but once it's on sourceforge or freshmeat I'll help him with that...

Rest assured, it won't appear on my CV...

P.S. Momentous occasion: This my 100th blog entry
P.S.S Momentous occasion 2: It's also my boss's 30th birthday. Don't you hate it when your boss is younger than you!

Auto-increment in Oracle

I'm sure that 100s of references exist all over the web for this, but someone asked me today how to do this, and so it's trivial for me to add it onto this blog...

In MySql you have an 'auto-increment' column. Does Oracle have one, and if not, how do you implement one in Oracle?

Well, we tend to say it's best to wrap up your INSERT statements in procedures and then manually grab the sequence number yourself... but if not:

You might notice the rather useful RETURNING clause in the insert statements. This bit seems to be missing from most internet examples I found...

CREATE SEQUENCE rob_tmp_seq
/

CREATE TABLE rob_tmp_tab ( id NUMBER, descr VARCHAR2(200) )
/

ALTER TABLE rob_tmp_tab ADD CONSTRAINT rob_tmp_tab_pk PRIMARY KEY ( id )
/

CREATE TRIGGER rob_tmp_tab_trig BEFORE INSERT ON rob_tmp_tab FOR EACH ROW
DECLARE
BEGIN
  --
  IF :new.id IS NULL THEN
    SELECT rob_tmp_seq.NEXTVAL
    INTO   :new.id
    FROM   DUAL;
  END IF;    
  --
END;
/

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
  --
  vn_number   NUMBER;
  --
BEGIN
  --
  INSERT INTO rob_tmp_tab( descr )
  VALUES                 ( 'This is a description' )
  RETURNING id INTO vn_number;
  --
  DBMS_OUTPUT.PUT_LINE( 'Created a record with the automatically assigned ID: ' || vn_number );
  --
  INSERT INTO rob_tmp_tab( id, descr )
  VALUES                 ( rob_tmp_seq.NEXTVAL, 'This is a description' )
  RETURNING id INTO vn_number;
  --
  DBMS_OUTPUT.PUT_LINE( 'Created a record with the ID grabbed from sequence manually ID: ' || vn_number );
  --
  INSERT INTO rob_tmp_tab( id, descr )
  VALUES                 ( 150, 'This is a description' )
  RETURNING id INTO vn_number;
  --
  DBMS_OUTPUT.PUT_LINE( 'Created a record with the ID specified manually ID: ' || vn_number );
  --
END;
/