REAL-WORLD COMPUTER PROGRAMMING FOR KIDS of ALL AGES

STEP 52: TRIGGERS & DATA SURVEILLANCE, and STORED PROCS

Welcome back, Database fans!

We’re actually winding down the Database-specific portion of our Journey with this Step, but don’t worry, we’ll still be dealing with Databases in the next major leg of our Journey, too, which will focus on Web Technology. In fact, we’re still going to be working with the same sort of Tables that we have been here lately (the Movies Database tables).

Before we move on to that angle or aspect of things, though, there are a couple more Database-centric things I want to cover with you. These are:

Triggers, Stored Procedures (or “Stored Procs*” as they’re usually called), and an often-used set of Database columns that are rarely or never seen by the casual user or observer, but which the Dataheads can refer to in order to see who is changing what sensitive data, and when they did it.

Note: “Stored Procs” rhymes with “Bored Crocs” (as in Crocodiles) rather than “Cored Prosses” which, while perhaps the more consistent way to pronounce it, is just plain wrong due to its aural dissonance. To say “Stored Prosses” would sound like you had a lisp or were trying to imitate Kaa.

THE WONDERFUL THING ABOUT TRIGGERS

Everybody has their “triggers.” Even mild-mannered Roy Rogers had his Trigger:

What are your Triggers? When somebody forgets to close the door when they leave your room? When they forget to put away the cereal box after getting it down from the cupboard -- or put the milk back in the fridge after getting it out? Or when they persist in the belief that Trigger was the name of a bouncy, trouncy, flouncy, pouncy Tiger in Winnie the Pooh?

The type of Triggers we are talking about is really not so different than the Triggers that provoke people. Those “triggers” cause you to feel a certain way or do a certain thing. Some people are referring to such when they say, “Wow, she really knows how to push his buttons!”

Databases have the same thing: Triggers that occur when an Insert, Update, or Delete statement is carried out on a specified Table. When set up to do so, it is as if a Trigger’s “button is pushed” when the designated event occurs.

In fact, on a Windows form when a Button is literally pushed, there is a reaction – whatever has been coded into the Button’s Click Event Handler. Triggers are similar: when something happens inside a Table, such as a Record is added or updated or deleted, a response can be triggered.

So why would you want to create Triggers in your database? What is their purpose, that is to say, their practical use?

The uses of Triggers are manifold (“manifold” is not just part of a car; it is also a fancy word for “many”). One specific example is the following scenario:

Imagine you are working as a developer at a place that has an EMPLOYEES Table in their Database. Imagine, also, that one of the columns in this Table is “Salary.” And, it being so that not every company is real security-conscious, imagine too that many people have access to this Table. What is to prevent somebody from updating that Table to increase their salary, changing it from, say, $2,000 to $20,000 per month? In most cases, people won’t have been given access to the database, but what if they have found a way around it or possibly they do have legitimate access to the Database?

In that scenario, it is possible (and it has happened) that somebody gives themselves a hefty raise in that way – by adding a zero or two to the “Salary” column of their own Employee Record, and ... Hello, big paycheck!

But this is wrong on many levels, so we want to know if some slack-jawed miscreant is up to such shenanigans. What to do? Create a Trigger that will alert somebody whenever the “Salary” column is modified by anybody other than the person auhorized and assigned to make such changes (somebody in the Personnel Department, no doubt). The Trigger can alert as to who changed the value of the Salary column, when they changed the Table, whose record was changed, and what it was changed from and to.

To prepare for creating such a Trigger, though, we first need to add some Columns to the Table that will record this nefarious activity. So let’s “back up” a minute and, before we get into the actual creating of the Trigger, add those specific Columns to a Table.

CATCHING THE CROOKS WITH DATABASE “SURVEILLANCE” COLUMNS

Let’s say we have a Table named EMPLOYEES, with the following minimal structure:

And let’s also say we have data in that Table like this:

Which columns do we want to add to “catch a thief” in the act of updating their Salary column’s value (or somebody else’s even, if two or more people are in cahoots in this malevolent mischievous malfeasance)?

The answer is:

CREATED

CREATED_BY

MODIFIED

MODIFIED_BY

So what are those columns? That is to say, What do they do? and Which data types are they?

Good question. Now I will give you a good and true answer:

The CREATED and MODIFIED columns hold, respectively, a “Timestamp” (date and time) of when the Record was first Created, and when it was last Updated.

The CREATED_BY and MODIFIED_BY columns, on the other paw (as you may have noticed in the contents of the employees Table, many of these potential miscreants have paws rather than hands) stores the name of the user who made these alterations.

So here is the Table after adding those new columns:

Note that I made these new columns ALL CAPS to differentiate them from the others, indicating that these are “special” (different) types of columns than the others. They are not created or modified by humans directly, but by a robot, so to speak (IOW, the database). Most people entering data won’t even know those columns exist. In fact, in that human ignorance of their very existence lies the power of those added columns – a person who wants to “outsmart” the system may soon find himself out-intelligenced by the “robot” instead (really, the programmer who designed and coded this trap for them).

But there’s no data in those columns, of course (all of those values are Null in the existing Records), because we have no Trigger set up to populate them:

Before we create the Trigger, it might help to get the “big picture” view of how Triggers work. You can picture a Trigger as being like a trap hidden underneath a bed of leaves at the base of a tree in the deep, dark woods. An unsuspecting bear or person steps on it, it clamps shut on their foot, and they say something like, “Ouch! My toesies!” (in the case of a bear, he says something more like, “If only I would have listened to my mother, I would have stayed away from trees that smelled like homo sapiens!”).

In the case of a Trigger on a Database Table, the “trap” slams shut at one of two times, based on how you define, or code, it: The code is either triggered before an Insert, Update, or Delete operation or after that operation has been completed (the Record has been inserted, updated, or deleted).

In our case, we are going to cause the Trigger to do its thing before the Created operation and after the Update event. What is meant above by “do its thing” is the population of the appropriate pair of the new columns we added: CREATED and CREATED_BY are populated before an Insert event, while MODIFIED and MODIFIED_BY are populated (or updated from a prior modification) after an Update event.

There are actually two ways you can cause these “Triggers” to fire. You can either go down the path we’ve been talking about, and create a Trigger like this:

CREATE TRIGGER employeesBeforeInsert

BEFORE INSERT

ON employees

FOR EACH ROW

BEGIN

SET NEW.CREATE = CURRENT_TIMESTAMP

SET NEW CREATED_BY = USER()

END

Notice that the Trigger has a name (employeesBeforeInsert), it indicates when it will take place (before an Insert event), which table it will affect (employees), and then which values will be assigned to which rows (current date and time to the CREATED column, the User’s name to the CREATED_BY column).

The Trigger for the Update event follows a similar pattern:

CREATE TRIGGER employeesAfterUpdate

AFTER UPDATE

ON employees

FOR EACH ROW

BEGIN

    IF OLD.salary <> NEW.salary THEN

        NEW.MODIFIED = CURRENT_TIMESTAMP

        NEW.MODIFIED_BY = USER

    END IF;

END

In this case, the difference is that the Trigger fires after an Update, and the old salary value is compared to the new salary value. If the value of salary has changed, the MODIFIED* columns are assigned the same values as we saw above for the before Insert with the CREATED* columns.

You might even create a separate Table to keep tabs on every time a sensitive value in the database changes, who changed it, and what they changed it from and to. We won’t go into that, though.

But as alluded to earlier, there is more than one way of petting a cat (but they usually don’t like it when you pet them “against the grain”; they’re finicky that way about their coiffure). But anyway, another way to deal with this challenge is to incorporate the “Triggers” right in the DDL for your Table definition when you create the Table, like so:

CREATE TABLE 'employees' (

  'employee_id' int NOT NULL AUTO_INCREMENT,

  'fname' varchar(30) NOT NULL,

  'lname' varchar(30) NOT NULL,

  'salary' decimal(13,2) NOT NULL,

  'CREATED' timestamp DEFAULT CURRENT_TIMESTAMP,

  'CREATED_BY' varchar(30) DEFAULT USER

  'MODIFIED' timestamp NULL ON UPDATE CURRENT_TIMESTAMP,

  'MODIFIED_BY' varchar(30) NULL ON UPDATE USER,

  PRIMARY KEY ('employee_id')

)  

The default values of the CREATED* columns take care of inserting those values, and the “On Update” stuff on the MODIFIED* columns take care of that (albeit without the granularity of checking that the salary column’s value has changed).

STORED PROCS AND THEIR ALMOST AMAZING BENEFIT

StoredProcs are usually thought of together with Triggers. They go together like beans and corn bread, chocolate and peanut butter, pizza and Caesar’s Salad, etc.

Stored Procs are basically just normal DML operations: Creation of Tables, Inserts, Updates, and Deletes of Tables or Records in Tables. So we won’t go into them in the detail. I just want to emphasize how extremely valuable they can be – especially when your database runs into performance “issues” (meaning, it takes too long to perform its duties).

The advantage of using Stored Procs is, then --or thus--, that they run faster than calling the code otherwise – sometimes dramatically so. If you’ve got a database operation that takes “forever” to complete, consider converting it to a Stored Proc; it’s likely that the difference will astound you. Sometimes operations that will never complete at all, due to their complexity and the amount of data they use, or the less-than-top-level hardware it runs on (or other problems) will run to completion if placed in a Stored Proc.

How exactly Stored Procedures are coded differs slightly from Database to Database (in other words, MySql is a little different from MS SQL Server, which is a little different from Firebird, etc.) but basically you “wrap” a “regular old” SQL Statement in a Stored Procedure. The reason the Stored Proc is faster than running the same code in “straight” DDL or DML is that the Stored Proc is processed, or compiled, ahead of time, and is thus “raring to go” when it is called. You might compare the difference between calling code as we have done in the past to run those statements and calling a Stored Proc to the difference between waking someone up out of a deep sleep and calling someone who is in bed, but has their clothes on already and is awake, just waiting to hear the call to breakfast.

In our next Step, the last one before we move into the wonderful world of the world wide web, we will entertain questions from our readers from around the world, and even across time! Neither time nor space have any hold on us.

Until then, my friends!

Earth-shakingly Important Notice: If you have a basic programming question (suitable to an audience of “Kids”), send it to idiolectable@gmail.com, specifying whether you would like your name and location used if it is printed in a future “Step” of this newsletter. If you are a subscriber to the newsletter, you can also leave a question at the bottom of this Step, in the “Comments” section.

If you do not want to give your real name, a nickname is acceptable (the first “Letter to the Editor” of mine that was printed appeared in Rolling Stone magazine, back in the early 1970s, and I signed it “Sylvester” for some reason which I no longer remember).  

Finally, it’s always interesting to see where people are from, so please provide your City or Town and the State it’s in, too (or Province, or whatever the region where you live is called).

To listen to this Step, the audio of it can be found here: