MySQL: Trigger (Part I)

Target:

    1) Definition of Trigger

    2) Circumstances of Using Trigger

    3) Syntax for Creating Trigger

 

1. Definition of Trigger

    1) Function: Observe one specific state change and then trigger a specific operation.

    Comments:

        1) We want  this to be a transaction: Atomicity

            1) insert into order(goods_id, order_count) values(1, 2); // Ordering

            2) update goods set goods_amount = goods_amount - 2 where goods_id = 1; // Update stock

        2) We want this to be a single operation thant cannot be interrupted.

            We can use transaction to integrate these two operations in JDBC/Hibernate.

            Or we can use trigger to achieve this.

    2) What kind of operation can be monitored by Trigger?

            1) C--->Create

            2) U--->Update

            3) D--->Delete

    3) What kind of operation can be done by Trigger?

            1) C--->Create

            2) U--->Update

            3) D--->Delete

 

2. Circumstances for Using Trigger ---> Some operations that cannot be divided/interrupted. Atomicity!

    1) Ordering -> Update stocking

    2) Credit card overdraw -> Moved into blacklist that cannot overdraw.

 

3. Syntax for Creating Trigger

    1) Which scope to be monitored                            --> Table

    2) What operation to be monitored                        --> CUD

    3) When to be triggered?                                       --> before/after

    4) What operation to be done once be triggered   --> CUD

    Comments: Think about school monitor guard example.

    5) Syntax for Deleting Trigger:          drop trigger trigger_name;

4.

    1) Example 1:  A Simple Hard Code Trigger

# Create initial table
create table goods(goods_id int primary key auto_increment, goods_name varchar(20) not null default '', goods_amount int not null default 0);

create table order_table(order_id int primary key auto_increment, goods_id int, order_count int);

# Aggregate bootstrap data
insert into goods(goods_name, goods_amount) values ('ASUS', 23), ('ThinkPad', 10);
+----------+------------+--------------+
| goods_id | goods_name | goods_amount |
+----------+------------+--------------+
|        1 | ASUS       |           23 |
|        2 | ThinkPad   |           10 |
+----------+------------+--------------+

# Ordering & Updating
insert into order_table(goods_id, order_count) values(2, 2);
update goods set goods_amount = goods_amount - 2 where goods_id = 2;
+----------+------------+--------------+
| goods_id | goods_name | goods_amount |
+----------+------------+--------------+
|        1 | ASUS       |           23 |
|        2 | ThinkPad   |            8 |
+----------+------------+--------------+

# Four essential elements for trigger:
# Scope to be monitored: table order_table;
# Operation to be monitored: insert
# Operation to be done when triggered: update
# Occasion to be triggered: after

# Syntax for Creating Trigger
create trigger trigger_name 
before/after insert/update/delete on table_name
for each row
begin
sql_statement_1;
sql_statement_2;
sql_statement_3;
...;
end;

# Example : Hard Code Just for Explaining Syntax for Creating Trigger
create trigger ordering 
after insert on order_table
for each row
begin
update goods set goods_amount = goods_amount - 2 where goods_id = 2;
end;

# Problems to discuss: begin sql_statement end;
# sql_statement has be be ended with ';'
# But the compiler once encounter ';' will end parsing

# Before creating trigger, change delimiter temporarily
# After creating trigger, recovery delimiter
delimiter $
create trigger ordering
after insert on order_table
for each row
begin
update goods ste goods_amount = goods_amount - 2 where goods_id = 2;
end$

delimiter ;

    2) A More Complicate Trigger:

        1) How to use data which are newly stored in other table?

            1) Insert

                1) For insert statement, the inserted column is tagged as new.

                2) For every single column data in inserted row, tagged as new.column_name. 

# Before trigger creation
select * from goods;
+----------+------------+--------------+
| goods_id | goods_name | goods_amount |
+----------+------------+--------------+
|        1 | ASUS       |           23 |
|        2 | ThinkPad   |            8 |
+----------+------------+--------------+

# Delete previously created trigger
drop trigger ordering;

# Create trigger
delimiter $
create trigger ordering
after insert on order_table
for each row
begin
update goods set goods_amount = goods_amount - new.order_count where goods_id = new.goods_id;
end$

# Recovery delimiter
delimiter ;

# Verify trigger works correctly
insert into order_table(goods_id, order_count) values(1, 3);
select * from goods;
+----------+------------+--------------+
| goods_id | goods_name | goods_amount |
+----------+------------+--------------+
|        1 | ASUS       |           20 |
|        2 | ThinkPad   |            8 |
+----------+------------+--------------+

    3) A Far More Complicate Example 

        1) Requirement:

            1> Every time add an order, subtract corresponding goods number from goods_amount

            2> Every time delete an order, add corresponding goods number to goods_amount

         2) Delete---> Compare with example above about Insert.

            1> For delete statement, the deleted column is tagged as old.

            2> For every single column data in deleted row, tagged as old.column_name. 

# Bootstrap Data
select * from goods;
+----------+------------+--------------+
| goods_id | goods_name | goods_amount |
+----------+------------+--------------+
|        1 | ASUS       |           20 |
|        2 | ThinkPad   |            8 |
+----------+------------+--------------+

select * from order_table;
+----------+----------+-------------+
| order_id | goods_id | order_count |
+----------+----------+-------------+
|        1 |        2 |           2 |
|        2 |        1 |           3 |
+----------+----------+-------------+

# Drop Previous Trigger
drop trigger ordering;

# Change Delimiter Temporarily
delimiter $

# Create Trigger that Monitor Deleting Event on order_table
create trigger del_order
after delete on order_table
for each row
begin
update goods set goods_amount = goods_amount + old.order_count where goods_id = old.goods_id;
end$

# Create Trigger that Monitor Inserting Event on order_table
create trigger add_order
after insert on order_table
for each row
begin
update goods set goods_amount = goods_amount - new.order_count where goods_id = new.goods_id;
end$

# Recovery Delimiter
delimiter ;

# Verify that Insert Trigger works correctly
insert into order_table(goods_id, order_count) values (1, 9);
select * from order_table;
+----------+----------+-------------+
| order_id | goods_id | order_count |
+----------+----------+-------------+
|        1 |        2 |           2 |
|        2 |        1 |           3 |
|        3 |        1 |           9 |
+----------+----------+-------------+
select * from goods;
+----------+------------+--------------+
| goods_id | goods_name | goods_amount |
+----------+------------+--------------+
|        1 | ASUS       |           11 |
|        2 | ThinkPad   |            8 |
+----------+------------+--------------+

# Verify that Delete Trigger works Correctly
delete from order_table where order_id = 1;
select * from order_table;
+----------+----------+-------------+
| order_id | goods_id | order_count |
+----------+----------+-------------+
|        2 |        1 |           3 |
|        3 |        1 |           9 |
+----------+----------+-------------+
select * from goods;
+----------+------------+--------------+
| goods_id | goods_name | goods_amount |
+----------+------------+--------------+
|        1 | ASUS       |           11 |
|        2 | ThinkPad   |           10 |
+----------+------------+--------------+

     4) A Much More Complicate Example 

        1) Requirement:

            1> Every time update an order, update corresponding goods number from goods_amount

         2) Update---> Compare with example above about delete/insert.

# Bootstrap State
select * from order_table;
+----------+----------+-------------+
| order_id | goods_id | order_count |
+----------+----------+-------------+
|        2 |        1 |           3 |
|        3 |        1 |           9 |
+----------+----------+-------------+
select * from goods;
+----------+------------+--------------+
| goods_id | goods_name | goods_amount |
+----------+------------+--------------+
|        1 | ASUS       |           11 |
|        2 | ThinkPad   |           10 |
+----------+------------+--------------+

# Modify Delimiter
delimiter $

# Create Trigger to Monitor Update Operation for order_table
create trigger modify_order
after update on order_table
for each row
begin
update goods set goods_amount = goods_amount + old.order_count - new.order_count where goods_id = old.goods_id;
end$

# Recovery Delimiter
delimiter ;

# Verify this Update Trigger works Correctly
update order_table set order_count = 1 where order_id = 2;
select * from order_table;
+----------+----------+-------------+
| order_id | goods_id | order_count |
+----------+----------+-------------+
|        2 |        1 |           1 |
|        3 |        1 |           9 |
+----------+----------+-------------+
select * from goods;
+----------+------------+--------------+
| goods_id | goods_name | goods_amount |
+----------+------------+--------------+
|        1 | ASUS       |           13 |
|        2 | ThinkPad   |           10 |
+----------+------------+--------------+

# Bingo! Enjoy!

 

Summary:

    How to retrieve/use data when creating trigger?

    1) For insert, the newly inserted row is tagged as new, and each column value in this row tagged as new.column_name;

    2) For delete, the deleted row is tagged as old, and each column value in this row tagged as old.column_name;

    3) For update, the updated row:

        1> Use old to represent the row that is before update.

             Use old.column_name to represent the column value in this row before update.

        2> Use new to represent the row that is after update.

             Use new.column_name to represent the column value in this row after update.

 

Action List:

    1) The difference between before/after?