CREATE TRIGGER NEW_HIRED AFTER INSERT ON EMPLOYEE FOR EACH ROW MODE DB2SQL UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1
Consider an application which records and tracks changes to stock prices. The database contains two tables, CURRENTQUOTE and QUOTEHISTORY.
Tables:
CURRENTQUOTE (SYMBOL, QUOTE, STATUS)
QUOTEHISTORY (SYMBOL,
QUOTE, QUOTE_TIMESTAMP)
When the QUOTE column of CURRENTQUOTE is updated, the new quote should be copied, with a timestamp, to the QUOTEHISTORY table. Also, the STATUS column of CURRENTQUOTE should be updated to reflect whether the stock is:
CREATE TRIGGER STOCK_STATUS
NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE
REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET NEWQUOTE.STATUS =
CASE
WHEN NEWQUOTE.QUOTE >
(SELECT MAX(QUOTE) FROM QUOTEHISTORY
WHERE SYMBOL = NEWQUOTE.SYMBOL
AND YEAR(QUOTE_TIMESTAMP) = YEAR(CURRENT DATE) )
THEN 'High'
WHEN NEWQUOTE.QUOTE <
(SELECT MIN(QUOTE) FROM QUOTEHISTORY
WHERE SYMBOL = NEWQUOTE.SYMBOL
AND YEAR(QUOTE_TIMESTAMP) = YEAR(CURRENT DATE) )
THEN 'Low'
WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE
THEN 'Rising'
WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE
THEN 'Dropping'
WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE
THEN 'Steady'
END;
END
In another example, we want to store a user password in the database. However, we don't want it stored in clear text, we would rather have it encrypted. Various databases allow a number of different encryption schemes. In this example, MySQL applies an SHA-2 cryptographic hash function to the clear text password before storing it. For example,
select SHA2('abc', 0);
produces the hashed string:
ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad
which is always 64 characters long. (Different hash functions may produce different lengths of string.)
Because the hashing and encryption of the password is done by a trigger, the
application program is not involved. In this example, the stamp
column records when the record was updated.
CREATE TRIGGER `trUserInsert`
BEFORE INSERT ON `user_base`.`user` FOR EACH ROW
BEGIN
SET NEW.userStamp = now();
-- A hashed password is 64 characters long.
IF LENGTH(NEW.userPassword) != 64 THEN
SET NEW.userPassword = SHA2(NEW.userPassword,0);
END IF;
END
and:
CREATE TRIGGER `trUserUpdate`
BEFORE UPDATE ON `user_base`.`user` FOR EACH ROW
BEGIN
SET NEW.userStamp = now();
-- A hashed password is 64 characters long.
IF LENGTH(NEW.userPassword) != 64 THEN
SET NEW.userPassword = SHA2(NEW.userPassword,0);
END IF;
END
These two triggers update the user password on insertion and update. The triggers check the length of the new user password because you do not want to apply the hash function to an already hashed value. This trigger would obviously fail to protect against that if the user enters a 64 character password string, so the application program need to put a limit of the size of the clear text password the user can enter.
You can then validate the password by comparing the value stored in the database against a clear text value entered by the user:
SELECT * FROM user WHERE userPassword = SHA2($userInput);
This selection fails if the hashed version of the value entered by the user
($userInput) doesn't match the value already stored in the
database. Note that there is no way to recover the original password from
the database except by comparison to the correct clear text value.
Insertions and updates can fail for many reasons - invalid data types, NULL where NULL is not allowed, invalid foreign keys, repeated primary key values, etc. For insertions and updates that are not caught by any of the above, a trigger can be used to validate input and cancel an insertion or update. The difficulty is that the trigger must have some way of actually forcing the insertion or update to fail.
One approach is to have the trigger violate one of table's constraints. In
the following example, the trigger uses the TRIM function to
strip leading and trailing spaces off of the new value for the k_desc
column. If the resulting value is an empty string, it changes the k_desc
value to NULL. Because that column does not allow NULL, the insertion fails
with the error message
:
SQL Error (1048): Column 'k_desc' cannot be
NULL
CREATE TRIGGER `trInsertKDesc`
BEFORE INSERT ON `keyword` FOR EACH ROW
BEGIN
IF TRIM(new.k_desc) = '' THEN
SET new.k_desc = NULL;
END IF;
END
(SQL Error 1048 refers to the fact that the column cannot be NULL)
The advantage to this approach is that it should work for most SQL dialects, as it simply makes use of existing constraints to cause an insertion failure.
The drawback to this approach is that the resulting error message can be misleading, as it is the error message for the constraint failure, and may not address the actual problem.
An alternative approach for MySQL is to force an insertion failure through
the SIGNAL command. SIGNAL is a MySQL command that
allows you to send messages to the server or client as appropriate. These
messages can include the affected table name, column name, error code, or a
custom message. The following trigger definition also forces the k_desc
column to contain a non-empty string:
BEGIN
IF TRIM(new.k_desc) = '' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'keyword description cannot be an empty string';
END IF;
END
This trigger causes the message
to appear, and cancels
the insertion.
SQL Error (1644): keyword
description cannot be an empty string
(SQLSTATE '45000' refers to an Unhandled user-defined
exception condition
, i.e. you are telling the client that you are
generating a user-defined error. SQL Error (1644) means that
the system has caught an unhandled user-defined exception.
The advantage of this approach is that the error messages can be tailored to
the exact problem. You could define multiple error messages within an IF
- ELSE IF - END IF structure.
The disadvantage is that this approach is not as portable to other SQL dialects as the first approach. Given that the SQL trigger code itself may not be portable, this is not a terrible thing.
Until MySQL actually implements the CHECK constraint, using a
trigger is a good way of enforcing complex rules against tables and columns.