Archives

All posts for the month May, 2015

Not everyone uses SQL triggers today. I’m a huge trigger fan. It allows you to implement business logic and rules at the appropriate model level in the application. Some rules should be client side, other rules should always be database side. Its more efficient, not to mention faster. I’ll put some more notes up soon about how denormalizing your physical schema can lead to massive gains in web tier productivity. But on with the topic de jour…

If you have triggers in your database, you’ll know that 99% of the time the entire trigger code should always, always, be enforced. that’s the point of the trigger. The application cannot get around it – that’s why we put those hard and fast rules in the triggers and constraints. Integrity is a beautiful thing.
However, as with every rule, there is always an exception! One likely exception that I occasionally hit is when doing a system upgrade, or other IT driven update to records, I do NOT want to disrupt the business logic and mess up the last modified dates, or other triggered logic columns. I probably want to “fix” a couple of columns in every row, or a given set of rows for some reason.
In this Use Case, my goal is to have the update fire, have the trigger NOT fire, and thus my fix gets applied, without disrupting the normal integrity of the data.
So how can we do this? There is no way to pass a parameter to a trigger on an adhoc basis! Sure, you can DROP the trigger, do your fix, then reload the trigger, but because that affects every user of the system, you now need full system down/maintenance window with all the inconvenience that entails! Whether you are MySQL, SQLServer, Oracle, DB2… I’ve done them all… there is no way. You cannot ‘drop’ a trigger for just one user. Unless you make one!

Let’s say you have a table “subscriber”, and you have a before insert and before update trigger that sets the create_datetime and last_modified_datetime each time a new row is added or updated. Pretty standard trigger stuff.
And you need to “fix” another column’s value without the create/update date fields getting updated.

Step 1: create a new column in your table definition called “operation_name”. Make it nullable. and typically, I make it varchar(30) or similar. think of this as a transient field. It has no business value.

In your application code, or SQL statements, you can now set the operation_name to any value you like. For example, “NO TRIGGER”.
In your SQL Trigger, which will fire in a discrete transaction, you can now see the “new” value of field operation_name and can act accordingly. In my example I want to skip the entire trigger.
In MySQL, the elegant way to skip the trigger from any point within it is to use the LEAVE statement.
It seems not a lot of people know how to use labels and LEAVE in SQL procedures and triggers, but they are incredibly useful for assisting in structuring maintainable SQL.

Normally, in a trigger you’d code something like…

CREATE trigger bi_subscriber before insert on subscriber for each row
begin
    set new.create_date = NOW();
    set new.update_date = NOW();
    /* ipse lorem... more code... */
end
$$

If however you “label” your begin/end statements, you can do a wondrous thing… you can leave that named block of code!

CREATE trigger bi_subscriber before insert on subscriber for each row
thetrigger: begin
    if new.operation_name = "NO TRIGGER" then
        LEAVE thetrigger;
    end if;
    set new.create_date = NOW();
    set new.update_date = NOW();
    /* ipse lorem... more code... */

end
$$

Now isn’t that a wonderful thing? Obviously you can now use the operation_name to influence trigger behavior instead of just leaving the trigger altogether. Now the client application can dynamically change the way the trigger rules fire. Very cool.

And because MySQL InnoDB is transaction oriented, users cannot clash on these things any more than they can when both trying to update the same row. Each update runs as its own discrete operation. The trigger is still fully active for all other users and insert/updates, just for this one transaction, the trigger knows to exit and do nothing. the nice thing being that this approach works from command line SQL tools too! So for those oddball DBA “fix” jobs it’s ideal.
This example is for MySQL, but the same thing applies to SQLServer and Oracle and most other SQL platforms.

I started doing this trick back in 1992 with SQLServer! Oracle didn’t get triggers that were worth anything until later in the 90’s.

Hope you like it.

This is one of those tiny little tips for “how to… ?” that I run into occasionally.
In PHPRunner (or ASPRunner!) events, like CustomAdd, where you are often doing more complex SQL processing, like inserting records into the current or related tables, you often need to know the ID of the record you just manually inserted in a CustomQuery(). I’m assuming that you are using AutoIncrement ID primary keys in this case.
For example, in an Employee Add process, in the customAdd event, I may have an additional step that inserts a row into a “user” table which is associated to the employee row.

$sql = "insert into user (field1, field2) values (somevalue, somevalue)";
 CustomQuery($sql);

Now perhaps you need to set the userID column in the Employee record to the ID of the User record just inserted. How do you get that value?

Recall that all your database work is happening on a global object in PHPRunner called $conn.
If you declare that as global in your script (first line!) then you get access to $conn in your script.

global $conn;

// rest of your script.

Now you can use regular MySQL functions to get the insert id out of $conn. Its a similar process for other supported databases.

all you need to code in your script is:-

$last_inserted_id = mysqli_insert_id($conn);

That’s it! Now you know the value of the autoincremented ID for the last inserted record that was done using $conn.

enjoy!

I love elegant, clean, code solutions, and this is one my favorites.  We’ve all had the issue of how to compute a person’s age when you know their date of birth, and you have a date at which you need to know what their age will be.

For example, I might know DOB and want to compute someone’s age at the next billing date.

This is it… one line of PHP brilliance 😉

$age = date_diff(date_create($_SESSION["current_subscriber_birthdate"]), date_create($_SESSION["next_bill_date"]))->y;

Continue Reading

Okay, this is one of the most difficult things to do in any event driven, web development environment. And it’s no different in PHPRunner. Most of the event code we’re playing with is PHP code. The snag is, you can’t just stick an “alert” in any old event line and have it appear on the screen! javascript is interfering with things… PHP code is on the server not the browser, flow of control has to happen before that alert gets back to the browser, lots of things basically get in the way of tracing what’s really happening in your front end and back end code. Continue Reading

This topic is all about how you can put your PHP code into separate source files and use those functions in your event scripts in PHPRunner applications. When your applications start to get more complicated, a few things happen.

a) you start to get longer and more complicated event scripts, and in my personal opinion, once you are over a few dozen lines of PHP, you will probably do better structuring your scripts into functions.

b) you start to get script features you want to include in more than page or button, or even in more than one PHPRunner application – having your code in a separate PHP file makes this trivial and means you aren’t duplicating code. Continue Reading