Mysql triggers and transaction operations, mysql trigger transactions

Source: Internet
Author: User
Tags savepoint

Mysql triggers and transaction operations, mysql trigger transactions

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  
  2.  
  3. update person set name='efgh' where id =10 
  4.  
  5. select * from person  
  6.  
  7. rollback  
  8.  
  9. select * from person 

Example:

 
 
  1. alter table person type=INNODB 
  2.  
  3. begin  
  4.  
  5. update person set name='efgh' where id =10 
  6.  
  7. select * from person  
  8.  
  9. commit  
  10.  
  11. select * from person  
  12.  
  13. begin  
  14.  
  15. delete from person where id=21 
  16.  
  17. update person set name='efgh' where id =10 
  18.  
  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;  
  2.  
  3. update score set score=40 where scoreid=1;  
  4.  
  5. savepoint s1;  
  6.  
  7. update score set score=50 where scoreid=2;  
  8.  
  9. select * from score;  
  10.  
  11. rollback to savepoint s1;  
  12.  
  13. select * from score;  
  14.  
  15. commit; 

Mysql trigger transaction processing C # implementation

The trigger is automatically processed on the SQL server. For example, if a user is deleted, all consumption records of the user are deleted.
Only one delete trigger is used. When this user is deleted, its consumption records are automatically deleted.

Sample Code for executing an SQL transaction:
// Execute Transaction Processing
Public void DoTran ()
{// Establish and open the connection
SqlConnection myConn = GetConn ();
MyConn. Open ();
SqlCommand myComm = new SqlCommand ();
// SqlTransaction myTran = new SqlTransaction ();
// Note that the SqlTransaction class has no public Constructor
SqlTransaction myTran;
// Create a transaction
MyTran = myConn. BeginTransaction ();
Try
{
// From then on, data operations based on the connection are considered as part of the transaction.
// Bind the connection and transaction object below
MyComm. Connection = myConn;
MyComm. Transaction = myTran; // locate the pubs Database
MyComm. CommandText = "USE pubs ";
MyComm. ExecuteNonQuery (); // update data
// Convert all computer books
MyComm. CommandText = "UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id LIKE 'pc % '";
MyComm. ExecuteNonQuery ();
// Submit the transaction
MyTran. Commit ();
}
Catch (Exception err)
{
Throw new ApplicationException ("transaction operation error, system information:" + err. Message );
}
Finally
{
MyConn. Close ();
}
}


Can transactions be used in MySQL triggers? When the data of one table is changed, the data changes of the other table are triggered.

Yes, but it is troublesome. It is recommended to use php + mysql for processing. Mysql is not a strength
 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.