TRIGGER: Mysql TRIGGER

1. TRIGGER introduction trigger is a mysql TRIGGER. When a trigger is created, it monitors specific events. When a TRIGGER is triggered, an object is activated to process trigger_stmt, that is, the statement to be executed. For example, when you add a record to a table, you can use trigger to update the sum of records. 2. Trigger creation statement CREATETRIGGERtrig

1. TRIGGER introduction trigger is a mysql TRIGGER. When a trigger is created, it monitors specific events. When a TRIGGER is triggered, an object is activated to process trigger_stmt, that is, the statement to be executed. For example, when you add a record to a table, you can use trigger to update the sum of records. 2. Trigger creation statement create trigger trig

1. TRIGGER introduction trigger is a mysql TRIGGER. When a trigger is created, it monitors specific events. When a TRIGGER is triggered, an object is activated to process trigger_stmt, that is, the statement to be executed. For example, when you add a record to a table, you can use trigger to update the sum of records. 2. Trigger creation statement

CREATE TRIGGER trigger_name trigger_time trigger_event    ON tbl_name FOR EACH ROW trigger_stmt

Trigger_name: name of the trigger
Trigger_time: trigger activation time, which is BEFORE or AFTER, indicating that BEFORE or AFTER Table Data Processing
Trigger_event: trigger activation type: the following three types
INSERT: the trigger program is activated when a new row is inserted into the table, for example, through INSERT, load data, and REPLACE statements.
UPDATE: The trigger program is activated when a row is changed, for example, through the UPDATE statement.
DELETE: the trigger program is activated when a row is deleted from the table, for example, through the DELETE and REPLACE statements.
Tbl_name: operation table name
Trigger_stmt: Statement for trigger operation 3. Use Limit 1) the same table cannot have the same trigger:
For example, a table cannot have two before update triggers. However, there can be one before update trigger program, one before insert trigger program, one before update trigger program, and one after update trigger program.
2) It must be a permanent table, not a TEMPORARY table or view. 4. Use trigger to create tables and triggers

 CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); CREATE TRIGGER ins_sum BEFORE INSERT ON account     FOR EACH ROW SET @sum = @sum + NEW.amount;

The NEW keyword is the NEW value after the operation. OLD indicates the value before the operation. Insert three rows of records and read the value of @ sum.

mysql> SET @sum = 0;mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);mysql> SELECT @sum AS 'Total amount inserted';

The value of @ sum returned is 14.98 + 1937.50-100 = 1852.48.
If the trigger program is multiline, use the BEGIN... END structure

mysql> delimiter //mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account    -> FOR EACH ROW    -> BEGIN    ->     IF NEW.amount < 0 THEN    ->         SET NEW.amount = 0;    ->     ELSEIF NEW.amount > 100 THEN    ->         SET NEW.amount = 100;    ->     END IF;    -> END;//mysql> delimiter ;

Update a record and view the value:

update account set acct_num = 241 where acct_num=141;  

Because the trigger is triggered by the above update, the amount value is set to 100.5. view the created trigger.

show triggers

You can also view the information in the information_schema database triggers table. 6. delete a trigger

drop trigger test.ins_sum;

Test indicates the database name, ins_sum indicates the trigger name, and 7. Failed Processing

· If BEFORE fails to trigger the program, the operation on the corresponding row is not executed.

· The AFTER trigger program is executed only when both the BEFORE trigger Program (if any) and the row operation are successful.
· If an error occurs during execution of the BEFORE or AFTER trigger program, the entire statement of the trigger program will fail to be called.

· For a transaction table, if the triggering program fails (and the entire statement fails), all the changes executed by the statement will be rolled back. This type of rollback cannot be performed for non-transactional tables. Therefore, any changes made before the failure even if the statement fails.

Address: http://blog.csdn.net/yonggang7/article/details/24552817