Action for triggers and transactions in mysql _ MySQL

TRIGGER Syntax creation trigger: CREATETRIGGERtrigger_nametrigger_timetrigger_eventONtbl_nameFOREACHROWtrigger_stmt; trigger_ti TRIGGER Syntax

Create a trigger:

Create trigger trigger_name trigger_time trigger_event

ON tbl_name for each row trigger_stmt;

Trigger_time is the time when the program is triggered. It can be BEFORE or AFTER trigger_event that specifies the type of the statement used to activate the trigger program.

Trigger_event can be one of the following values:

· 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.

Example:

Mysql-> create trigger test

-> Before update on table_name for each row

-> Update table_name set NEW. updateTime = NOW () where id = NEW. ID;

If the monitored table and updated table are the same, it can be omitted

Mysql-> create trigger test

-> Before update on table_name for each row

-> Set NEW. updateTime = NOW () where id = NEW. ID;

Delete trigger:

Drop trigger trigger_name;

Example:

Mysql-> drop trigger trigger_name;

View the trigger:

Example:

Mysql-> desc triggers;

Or

Mysql-> select * from triggers where trigger_name = 'xxxxxx ';

Mysql-> show create trigger trigger_name;

========================================================== ========================================================== ======

Transaction features: ACID

• Atomicity (Atomicity)
• Consistency (stability, Consistency)
• Isolation (Isolation)
• Durability)

  Note: transactions are only valid for statements that affect data.

  Show engines // view the data engine supported by mysql locks.

  MyISAM does not support transactions. InnoDB supports transactions.

  By default, MySQL runs in the automatic submission mode, which means that no small command is executed as a single command.

  To enable mysql to support transactions, you only need to modify the data engine (alter table person type = INNODB ).

  Use the start transaction or begin command to start a transaction, and use commit or rollback to end the transaction.

  The end of a transaction: except for commit, rollback ends and DDL or DCL statements ends.

  Save point: you can use the savepoint name command in the transaction to set some save points. in the future, when you use rollback to savepoint name to end a transaction, data before name is saved, subsequent data is not saved.

  Mysql uses transaction keywords

  • Begin // open a transaction.
  • Commit // submit it to the database.
  • Rollback // cancel the operation.
  • Savepoint // Save, partially cancel, partially submit.
  • Alter table person type = INNODB // modify the data engine.

    Example:

    1. Begin
    3. Update person set name = 'efgh' where id = 10
    5. Select * from person
    7. Rollback
    9. Select * from person

    Example:

    1. Alter table person type = INNODB
    3. Begin
    5. Update person set name = 'efgh' where id = 10
    7. Select * from person
    9. Commit
    11. Select * from person
    13. Begin
    15. Delete from person where id = 21
    17. Update person set name = 'efgh' where id = 10
    19. Commit/rollback

    The save point must be used for the preceding submission.

    Note:

    1. you can only cancel rollback to savepoint p1 from a storage point.

    2. a commit to savepoint p2 file cannot be submitted. // The incorrect syntax is as follows.

    3. finally, commit does not submit uncanceled storage points to the data.

    Example of transaction retention point:

    1. Begin;
    3. Update score set score = 40 where scoreid = 1;
    5. Savepoint s1;
    7. Update score set score = 50 where scoreid = 2;
    9. Select * from score;
    11. Rollback to savepoint s1;
    13. Select * from score;
    15. Commit;