Database Reference
In-Depth Information
▼
Analysis
This trigger is similar to the
neworder
trigger; it logs order deletions. This
trigger is executed
BEFORE DELETE
(or you'd not have access to the
order_num
).
If you were to delete the order you just inserted, you'd see a second row in the
orders_log
table reflecting the deletion.
Note
Multistatement Triggers Notice that the triggers shown here all use
BEGIN
and
END
statements to mark the trigger body. This is actually not necessary in the examples
used thus far, although it does no harm. The advantage of using a
BEGIN END
block
is that the trigger would then be able to accommodate multiple SQL statements (one
after the other within the
BEGIN END
block) as you see in the next example.
Another good use for
DELETE
triggers is to archive deletions (rows deleted
from a table are automatically saved in their entirety to an archive table). This
updated version of the
deleteorder
trigger logs the deletion and also saves
it to a table named
orders_archive
(you obviously need to create that table
for this trigger to work;
orders_archive
will use the same
CREATE TABLE
statement as the one used to create
orders
, although you'll want to drop the
AUTO INCREMENT
):
▼
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.
