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:
- begin
-
- update person set name='efgh' where id =10
-
- select * from person
-
- rollback
-
- select * from person
Example:
- alter table person type=INNODB
-
- begin
-
- update person set name='efgh' where id =10
-
- select * from person
-
- commit
-
- select * from person
-
- begin
-
- delete from person where id=21
-
- update person set name='efgh' where id =10
-
- 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:
- begin;
-
- update score set score=40 where scoreid=1;
-
- savepoint s1;
-
- update score set score=50 where scoreid=2;
-
- select * from score;
-
- rollback to savepoint s1;
-
- select * from score;
-
- 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