MariaDB Crash Course - Ben Forta [78]
Input
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO orders_log(changed_on, change_type, order_num)
VALUES(Now(),'D', OLD.order_num);
INSERT INTO orders_archive(order_num, order_date, cust_id)
VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;
Analysis
Before any order is deleted this trigger is executed. In addition to the logging seen previously, this trigger uses an INSERT statement to save the values in OLD (the order about to be deleted) into an archive table named archive_orders.
* * *
Tip: An Extra Level of Protection
The advantage of using a BEFORE DELETE trigger (as opposed to an AFTER DELETE trigger) is that if, for some reason, the order could not be archived, the DELETE itself will be aborted.
* * *
UPDATE Triggers
UPDATE triggers are executed before or after an UPDATE statement is executed. Be aware of the following:
• Within UPDATE trigger code, you can refer to a virtual table named OLD to access the previous (pre-UPDATE statement) values and NEW to access the new updated values.
• In a BEFORE UPDATE trigger, the values in NEW may also be updated (allowing you to change values about to be used in the UPDATE statement).
• The values in OLD are all read-only and cannot be updated.
The following example ensures that state abbreviations are always in uppercase (regardless of how they were actually specified in the UPDATE statement):
Input
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
Analysis
Obviously, any data cleanup needs to occur in the BEFORE UPDATE statement as it does in this example. Each time a row is updated, the value in NEW.vend_state (the value that is used to update table rows) is replaced with Upper(NEW.vend_state).
What about logging updates to the orders table? With what you have learned here you should be able to create a updateorder trigger that inserts a row into the orders_log table (make sure to insert a U for change_type).
More on Triggers
Before wrapping up this chapter, here are some important points to keep in mind when using triggers:
• Trigger support in MariaDB is rather rudimentary at best when compared to other DBMSs. There are plans to improve and enhance trigger support in future versions.
• Creating triggers might require special security access. However, trigger execution is automatic. If an INSERT, UPDATE, or DELETE statement may be executed, any associated triggers are executed, too.
• Triggers should be used to ensure data consistency (case, formatting, and so on). The advantage of performing this type of processing in a trigger is that it always happens, and happens transparently, regardless of client application.
• One interesting use for triggers is in creating an audit trail, as seen in this chapter. Using triggers it would be easy to log changes (even before and after states if needed) to another table.
• Unfortunately the CALL statement is not supported in MariaDB triggers. This means that stored procedures cannot be invoked from within triggers. Any needed stored procedure code would need to be replicated within the trigger itself.
Summary
In this chapter, you learned what triggers are and why they are used. You learned the trigger types and the times that they can be executed. You also saw examples of triggers used for INSERT, DELETE, and UPDATE operations.
26. Managing Transaction Processing
In this chapter you learn what transactions are and how to use COMMIT and ROLLBACK statements to manage transaction processing.
Understanding Transaction Processing
* * *
Note: Not All Engines Support Transactions