Special PSQL for Triggers - Firebird

Two special PSQL elements are available for triggers: the Boolean event context ariables INSERTING, UPDATING, and DELETING, and the NEW and OLD context variables.

Event Variables

Firebird 1.5 introduced the Boolean context variables INSERTING, UPDATING, and DELETING to support the conditional branching for the multi-event trigger feature. Possible syntax patterns would be

IF ({INSERTING | UPDATING | DELETING}
OR {UPDATING | DELETING | INSERTING}
[OR {DELETING | INSERTING | UPDATING}]) THEN ...

Follow through the examples in the rest of this chapter to see these useful predicates at work.

NEW and OLD Variables

The NEW and OLD context variables are a trigger-specific extension to PSQL that enables your code to refer to the existing (“old”) and requested (“new”) values of each column. The NEW.* variables have values in INSERT and UPDATE events; OLD.* variables have values in UPDATE and DELETE events. NEW.* in delete events and OLD.* in insert events are null. The applicable old and new counterparts are available for all columns in a table or view, even if the columns themselves are not referred to in the DML statement.

The OLD.* values (if available) can be manipulated as variables within the trigger, but changes of value do not affect the stored old values. The NEW.* values (if available) are read-write during the BEFORE phase and read-only during the AFTER phase. If you want to manipulate them as variable values in an After trigger, move the values into local variables and refer to those.

Uses for NEW and OLD

For harnessing the power of Firebird triggers to develop databases that take care of data integrity independently of humans and external programming, NEW and OLD variables are the essential tool. They can be used to

  • Provide valid default values under any conditions.
  • Validate and, if required, transform user input.
  • Supply keys and values for performing automatic updates to other tables.
  • Implement auto-incrementing keys by means of generators.

New values for a row can only be altered by before actions. A trigger that fires in the AFTER phase and tries to assign a value to NEW. Column will have no effect.

NEW values are writeable all through the BEFORE phase and take up a reassignment of their value immediately. The new record version will receive any reassignments only when all BEFORE triggers have completed. At this point, the NEW values become read-only. Hence, if you have multiple triggers adjusting the same NEW values, it is important to ensure that they all have different POSITION numbers, correctly ordered.

Implementing Auto-Incrementing Keys

A recommended usage of BEFORE INSERT triggers in Firebird is for implementing @IDENTITY -style auto-incrementing primary keys. The technique is simple and most Firebird developers can write these triggers in their sleep. It involves two steps:

  1. Create a generator to e used to generate the unique numbers for the key.
  2. Write a BEFORE INSERT trigger for the table.

To illustrate the technique, we’ll implement an auto-incrementing primary key for a table named CUSTOMER that has the primary key CUSTOMER_ID, an integer type or BigInt (v.1.5) or NUMERIC(18,0) (v.1.0.x.). For a dialect 1 database, CUSTOMER_ID would be an integer.

First, create the generator:

CREATE GENERATOR GEN_PK_CUSTOMER;

Now, create the trigger:

CREATE TRIGGER BI_CUSTOMER FOR CUSTOMER
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.CUSTOMER_ID IS NULL) THEN
NEW.CUSTOMER_ID = GEN_ID(GEN_PK_CUSTOMER, 1);
END ^
COMMIT ^

When an insert is performed, CUSTOMER_ID is deliberately omitted from the input list of the INSERT statement:

INSERT INTO CUSTOMER (
LAST_NAME,
FIRST_NAME,
...)
VALUES (?, ?, ...);

Without the trigger, this statement would cause an exception because primary keys are not nullable. However, the BEFORE INSERT trigger executes before the constraint validation, detects that CUSTOMER_ID is null, and does its stuff.

Why NEW.value Is Tested for Null

If the trigger can do that for me, you might ask, why should it need to test for null?

It can be of benefit for an application to know what the primary key of a new row will be without having to wait until its transaction commits. For example, it is a very common requirement to create a “master” record and link new “detail” records to it, usually by way of a foreign key, in a single transaction. It is clumsy—even risky sometimes—to break the atomicity of the master-detail creation task by committing the master in order to acquire the foreign key value for the detail records, as you need to do when relying solely on the trigger.

Applications written for Firebird take advantage of one peculiar characteristic of generators: They are not subject to any user transaction. Once generated, a value cannot be taken by another transaction, and it cannot be rolled back.

A quick query in its own transaction returns the value

SELECT GEN_ID(GEN_PK_CUSTOMER, 1) AS RESULT
FROM RDB$DATABASE;

If or trigger omitted the null test and just did this

...
AS
NEW.CUSTOMER_ID = GEN_ID (GEN_PK_CUSTOMER, 1);
END ^

Then the value posted by the application would get overwritten by the second “pull” off the generator and break the link to the detail records.

This situation is not an argument for dispensing with the triggered key. On the contrary, the trigger with the null test ensures that the business rule will be enforced under any conditions.

Transformations

A NEW variable can be used to transform a value into something else. A common trick is to use a trigger (or a pair of triggers, in the case of v.1.0.x) to maintain a “proxy” column for doing case-insensitive searches on another column that may be any mixture
of case. The trigger reads the NEW value of the mixed-case column, converts it to uppercase, and writes it to the NEW value of the search column. The column being “proxied” should have a NOT NULL constraint to ensure that there will always be a value to find:

CREATE TABLE MEMBER (
MEMBER_ID INTEGER NOT NULL PRIMARY KEY,
LAST_NAME VARCHAR(40) NOT NULL,
FIRST_NAME VARCHAR(35),
PROXY_LAST_NAME VARCHAR(40),
MEMBER_TYPE CHAR(3) NOT NULL,
MEMBERSHIP_NUM VARCHAR(13),
....);
COMMIT;
/* */
SET TERM ^;
CREATE TRIGGER BA_MEMBER1 FOR MEMBER
ACTIVE BEFORE INSERT OR UPDATE
POSITION 0
AS
BEGIN
...
NEW.PROXY_LAST_NAME = UPPER(NEW.LAST_NAME);
...
END ^

All kinds of transformations are possible. Suppose we want to issue membership numbers (MEMBERSHIP_NUM) made up of the MEMBER_TYPE followed by a string of ten digits, left-padded with zeros, based on the generated primary key of the MEMBER table. We can have them automatically generated in a BEFORE INSERT trigger:

CREATE TRIGGER BI_MEMBER2 FOR MEMBER
ACTIVE BEFORE INSERT
POSITION 2
AS
DECLARE VARIABLE ID_AS_STRING VARCHAR(10);
BEGIN
ID_AS_STRING = CAST(NEW.ID AS VARCHAR(10));
WHILE (NOT (ID_AS_STRING LIKE '__________%')) /* 10-character mask */
DO
ID_AS_STRING = '0'||ID_AS_STRING;
NEW.MEMBERSHIP_NUM = NEW.MEMBER_TYPE||ID_AS_STRING;
END ^

Validation and Defaults

Triggers can improve on standard SQL constraints when it comes to the issues validating input and applying default values.

Validation

SQL provides for CHECK constraints to ensure that only “good” data is stored. For example, columns created under this domain are restricted to uppercase characters and digits:

CREATE DOMAIN TYPECODE CHAR(3) CHECK (VALUE IS NULL OR VALUE = UPPER(VALUE));

This is fine—we want this rule to be enforced. On its own, the constraint will throw an exception if any client application tries to submit lowercase characters. With a trigger, we can void the exception altogether by fixing any attempted violations in situ:

CREATE TRIGGER BA_ATABLE FOR ATABLE
ACTIVE BEFORE INSERT OR UPDATE
AS
BEGIN
NEW.ATYPECODE = UPPER(NEW.ATYPECODE);
END ^

Default Values

In domains and column definitions, you can specify a DEFAULT value. While it seems a good idea to be able to default a non-nullable column to some zero-impact value, the SQL DEFAULT attribute is a toothless beast. It works if and only if two conditions are met:

  • The operation is an INSERT.
  • The column has not been included in the statement’s input list.

Since many modern application interfaces automatically compose an INSERT statement using the output columns of a SELECT statement as the basis of the input list, it follows that “something” is always passed for each column. If the application itself does not apply a default, the usual behavior is for the application to pass NULL. When the server receives NULL for a defaulted column, it stores NULL. Other column constraints may kick in and cause an exception to be thrown —especially a NOT NULL constraint—but a column default never overrides or corrects any value passed from the client interface.

The second problem, of course, is that column defaults are never applied when the operation is an update.

In short, triggers do a far more effective job at managing defaults than do default column attributes. Take, for example, a column defined under this domain:

CREATE DOMAIN MONEY NUMERIC(18,0)
NOT NULL DEFAULT 0.00;

A BEFORE INSERT OR UPDATE trigger on any column using the MONEY domain will take care of the default, no matter what comes through:

CREATE TRIGGER BI_ACCOUNT FOR ACCOUNT
ACTIVE BEFORE INSERT OR UPDATE
AS
BEGIN
IF (NEW.BALANCE IS NULL) THEN
NEW.BALANCE = O.00;
END ^

Auto-Stamping

Triggers are useful for “auto-stamping” contextual information into columns defined for the purpose. Firebird provides a number of context variables that you can use for this sort of operation. You can also provide “flags” of your own that you calculate or simply supply as constants during the course of the trigger’s execution.

In this example, we use an “after” multi-event trigger to auto-stamp the user name, a timestamp, and the transaction ID onto a log file, along with some information about the data event. Since process logging (if we do it) is likely to be the last thing we want to do in a DML event, the trigger has a high sequence number:

CREATE TRIGGER AA_MEMBER FOR MEMBER
ACTIVE AFTER INSERT OR UPDATE OR DELETE
POSITION 99
AS
DECLARE VARIABLE MEM_ID INTEGER;
DECLARE VARIABLE DML_EVENT CHAR(4);
BEGIN
IF (DELETING) THEN
BEGIN
MEM_ID = OLD.MEMBER_ID;
DML_EVENT = 'DEL ';
END
ELSE
BEGIN
MEM_ID = NEW.MEMBER_ID;
IF (UPDATING) THEN
DML_EVENT = 'EDIT';
ELSE
DML_EVENT = 'NEW ';
END
INSERT INTO PROCESS_LOG (
TRANS_ID,
USER_ID,
MEMBER_ID,
DML_EVENT,
TIME_STAMP)
VALUES (
CURRENT_TRANSACTION,
CURRENT_USER,
:MEM_ID,
:DML_EVENT,
CURRENT_TIMESTAMP);
END ^

Of course, you can auto-stamp your new or edited rows directly as well, in the course of a “before” trigger.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics