howto: SQL Triggers

So, I have a computer science test on Thursday and have been getting annoyed at triggers. Triggers are basically a wee bit of sql that ‘watches’ the database for a certain action, and then executes a block of sql when the action is performed.

The action can be an insert, update or delete and the trigger can be run before, after or instead of the statement that set off the trigger.

For example the following statement is triggered when there is a new row inserted into the testtable table and it will duplicate two attributes (attr and attr1) into a second table called result, once for each row that has been inserted.

create trigger testtrigger
  before insert on testtable
  for each row
    insert into result values(new.attr, new.attr1);

This is all very nice for one statement, but what if you need a couple, or some conditions? You can turn the single statement into a begin ... end block to run multiple sql statements:

create trigger testtrigger
  before update on testtable
  for each row
      if new.attr = 'somevalue'
        insert into result values(new.attr, old.attr);
        insert into result values('constant', 'mismatching data types');
      end if;

This snippet will run before an update on testtable and will execute one of two different statements depending on the new value of attr.

When the trigger started on insert or update, a tuple new is set to be the new row that is being inserted (Sometimes you need to call it :new). On update you get new and old to work with.


When you’re running this in some clients or interactive prompts, the interpreter will mistake the first semicolon as the end of the statement and fail. To fix this you just need to add:

delimiter //

create trigger mytrigger ...

delimiter ;

around your statement.

And that’s basically all I learnt about triggers. I don’t know why you’d want to do this kind of stuff in your database when you would do it with your database application.. but whatever.

Advanced Phone Maths

So, the iPhone 6 sports a revolutionary new 4.7” display. This upgrade was completely unexpected. I personally didn’t expect Apple to enter the ‘phablet’ market - instead thinking they’d keep with their model of targeting the average consumer, which is now someone who wants a slightly larger phone.

The increased screen size is definitely a big improvement. Games can worry less about your fingers covering up all the action and videos can be watched in their full 4.7” glory. However the phone is too big. It has the same footprint as my Galaxy S3 - from 2012 - a phone that when you take a step back from and look at the whole device it actually has a fairly small screen. Samsung even squeezed an extra .3” into the S4, while keeping the same footprint.

This led me to wonder how the iPhone stacked up against the other flagships of the last few years, in terms of the ratio of screen area to the area of the device. After some trips to GSM Arena and some spreadsheet-foo, I’d come up with an answer:

Graph of phones

Calculating the screen area was a bit tricky, in the end I calculated the number of pixels along the diagonal length of the screen and used this and the screen size to calculate the pixel density of the screen. Knowing the pixel density I could easily convert the horizontal and vertical resolutions to lengths and then calculate the area.

I chucked all this into a spreadsheet and bam, graph! The dark lines show the ratio of the screen size to the physical phone size, and the lighter grey line shows the device’s screen size. As you can see no iPhone model has a particularly large screen relative to the size of the device, which is obviously because of the massive chin for the home button and matching ‘forehead’.

So basically I just made this to illustrate that the 2013 Moto X is a really compact phone, with a nice sized screen. And the LG G3 is the phone you want to get if you don’t have time for bezels.

RE: standards

So I got a bit off-topic in my last post about standards, and how I think we need more of them to make all the cool new gadgets function together - maybe not work nicely, but at least function.

So naturally, I’ve been thinking about what I’d like to see standardised.

1. Wireless file transfers

Airdrop is pretty cool, so is Samsung’s S beam. Both are limited to a handful of devices (S beam is actually based on the standard android beam, but is extended to tranfer faster over an ad-hoc wifi network)

Sending a file to someone shouldn’t be difficult, but at the moment you’re stuck with email, a physical drive or a cloud storage (relevant xkcd). Being able to share a file to any device without cables or 3rd party apps would be excellent.

At the moment the easiest way to transfer a file to my phone from my laptop is either Dropbox or a home server. Neither are particularly convenient - I think most android phones should have a ‘USB mode’ and can act like a pen drive, hovever my last two phones and tablet couldn’t do this (all Samsung - correlation?)

2. Cloud storage (& other web services)

So, everything’s moving towards the cloud. You can pick up a Chromebook for a few hundred dollars and cast away ties to any local filesystems. However, your data has to be in Google’s servers. Hmm. Any app that you use either needs it’s own server or use Google’s.

I would be great if basic cloud save functionality was standardised, so that you could pick the service that data is stored in, decreasing the monopoly that Google will only get a tighter grip on when (not if) Chromebooks take off. This could mean that for some app, when you sign up you just say “put my data in ACME Cloud Storage, please!” and away it went, without the app developer having to hard-code interfaces for every cloud provider on earth.

This idea could be extended to social networks and other web apps, similarly to how android handles intents - if you have a network ‘installed’, you can share information to it from any other webapp. Again without the developer having to write tedious amounts of code.

3. Instant Messaging

Similar to #2 this would mean that different services would have at least some kind of common functionality - I don’t know.. like messaging people or something - and then each provider could have more awesome features on top, but at least you wouldn’t absolutely have to have multiple services just to talk to people. Imagine if email only worked between the same host? Gmail would be the only one left (I mean, who uses anything else?)

I don’t know quite how any of this would be implemented, but that’s another problem!

I Want Standards and Control

Can you iMessage me? Wait I have Android. You can’t. Damn. Facetime? Nope. This isn’t just an Apple thing? send a file on Android with NFC? Oh you don’t have S beam? As phones turn into more and more powerful mini-computers they seem to be still lagging behind when it comes to compatibility. It still matters if someone has iOS, Android or Windows Phone - not every app has been written three times.

There are three awesome things that are still functionally standard across all platforms:

  • SMS
  • Email
  • Phone calls (I know, who uses that!)

And this is what holds mobile technology together. There is no relying on a platform to come up with a solution and then change it (Twitter), because if you don’t like something you can always use a different app any of the three that fits your needs better. (Well, I presume you can on iOS – wait you can’t :’( sucks for you I guess.)

This is the main reason why I favour Android over other mobile OSs - it lets apps fully replace part of my phone, letting them completely integrate into the OS. You wouldn’t notice that I don’t use the stock camera, messaging app or launcher. They all just slide in together like a crazy jigsaw.

Now I will admit it is a bit of a pain having to set these little things up, and I understand Apple’s want to fully control how iOS works - if you download an average messaging app and can’t send texts, who do you blame? Apple.

Although, imagine that you went and bought a shiny new Surface 3 and then found out that Internet Explorer was stuck as the default browser. Every link you open INTERNET EXPLORER! Why would you do that to yourself? You masochistic freak.

Even OS X allows you to change the default browser (to Chrome), mail app (to Mailbox) and search engine (to Google). Although by the looks of Yosemite the latter may be locked to Bing.. I don’t know how I’ll manage. Seriously Apple - if you do this and don’t give me the option to change it, I will consider switching back to Ubuntu for a whole 5 minutes.

It’s my technology - I should be able to add and remove bits as I see fit. ie:

I should have superuser privileges.


Hello! I’m Will. Currently studying software engineering and generally making a nuisance of myself building random things that I think should exist.

This blog is here because it’d be nice to have a place to put stuff that I make and talk about it a bit.

I guess I’ll actually write something interesting later - time for a run now!