MySQL transactions and triggers

Directory (?) [+]

1. Transaction
1. Commit or roll back
2. Common transaction Directives
3. Characteristics of the business acid
2. Trigger
1. Create a Trigger
2. Manage triggers

Transactions

CreateTable Swpu (IDIntPrimaryKey Auto_increment,moneyDecimal10,2) Comment' tuition fees ');InsertInto SwpuValuesNull5000); create table swpu_stu (ID int primary key auto_increment,stu_ Money decimal (10,2) Comment  "Student Property"); insert into Swpu_stu values (null,7000); select * from swpu; select * from swpu_stu;        

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13

For example, students pay 100 yuan for miscellaneous expenses to school

update swpu_stu set stu_money=stu_money-100 where id=1;update swpu set money=money+100 where id=1;select * from swpu;select * from swpu_stu;

• 1
• 2
• 3
• 4

transaction: If the entire group succeeds, it means that all SQL is implemented. If either one fails, it means the entire operation fails. Failure means that the whole process is meaningless. The database should be brought back to its initial state before the operation.

how to deal with? 1, after the failure, you can return to the start position. 2. Before all success, other users (processes, sessions) are not able to see the data modifications within the operation.

specific idea: is to design a mark (backup point) before a set of operations. If the execution succeeds, let others see the data change. If the execution fails, others cannot see it and should return to the mark position.

implementation: Use the transaction log feature of the InnoDB storage engine. If successful, the result is submitted to the database. means that the database content is changed.

2 phases of SQL execution: 1, execution Phase 2, execution results, commit to database phase, where our transaction log is the result of saving the execution phase. if used to Select commits , the execution results are submitted to the database.

The default execution method is called auto-commit. Complete the submission process automatically. Therefore, the auto-commit feature needs to be turned off.

There is a system variable autocommit that can be configured for autocommit.

set autocommit=0;show variables like ‘autocommit‘;

• 1
• 2

After closing, the corresponding UPDATE statement is executed again, and when the data is viewed in other connections, there is no change because the result is not committed.

commit or roll back

On this basis, all SQL statements are executed. Determine if the success (errors, including syntax errors and logical errors, server errors ) are successful. Success: Submit the results. Use commit. Failed: Go back to start position. Use rollback.

Update Swpu_stuset stu_money=stu_money-100  where Id=1; update swpu set money=money+100 Span class= "Hljs-keyword" >where id=1; select * from swpu; select * from swpu_stu; COMMIT; select * from swpu;select * from swpu_stu;      

• 1
• 2
• 3
• 4
• 5
• 6
• 7

Before submitting:

After submission:

You need to change to auto-commit manually.

set autocommit=1;

• 1

Common transaction Directives

Open Transaction start transcation; Turn off autocommit, and if the transaction ends (success or failure), it goes back to the auto-commit mechanism, returning to the state of start. Success: Commit; Failure: rollback;

Start transcation;Update Swpu_stuset stu_money=stu_money-100  where Id=1; update swpu set money=money+100 Span class= "Hljs-keyword" >where id=1; select * from swpu; select * from swpu_stu; COMMIT; select * from swpu;select * from swpu_stu;      

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

Transaction Qualification: takes effect under the InnoDB (DBD) storage engine.

Note: The start transcation; enable transaction feature is required. Without having to set autocommit=0; turn on transaction functionality.

features of the business acid:

1, atomicity 2, consistency (from start to finish, the data will not be changed by other statements outside the transaction.) ) 3, Isolation (operation of one transaction does not affect another transaction) 4, persistence (modification is in effect permanently)

Trigger

Trigger: Listen for data to operate. On the current table, set up a listener for each row of data, listening for related events. Each time an event occurs, a section of function code that is completed by SQL is executed.

elements of the trigger : event, execution code.

Create a trigger

create trigger 名字 事件 执行性代码

event: Inserting insert Delete Delete Modify update

Timing: Before and after execution (after before). Six events were formed by timing and events.

insert，before delete，before updateafter insert，after delete，after update

• 1
• 2

The event stipulates: What is the timing of that table on what action?

Executable code: The code that consists of the SQL statement.

create trigger name_jiaoxuefei after update on swpu_stu for each rowupdate swpu set money=money+150;select * from swpu;select * from swpu_stu;

• 1
• 2
• 3
• 4
• 5
• 6

Trigger: A trigger that occurs at a specific time.

update swpu_stu set stu_money=stu_money-150 where id=1;

• 1

NOTE: triggers cannot have the same name. Currently MySQL only supports a class of events and sets a trigger.

create trigger name_jiaoxuefei2 after update on swpu_stu for each rowupdate swpu set money=money-150;

• 1
• 2
• 3

To manage triggers:

Delete: Drop

drop trigger trigger_name;drop trigger name_jiaoxuefei;

• 1
• 2
• 3

View: Show

show create trigger trigger_name;show create trigger name_jiaoxuefei;

• 1
• 2
• 3

Within the trigger, get the data that is triggered to the triggering program. This is done using new and old in the trigger program.

Old: Listen to the data on the table where the event occurred, before the event occurs, the data is older.

NEW: On the listener table, after the event occurs, the newly processed data is completed. The data is the record that triggered the event.

Event Update can use the new and old

Update Swpu_stuSet stu_money=stu_money-150where id=1;//1000 950 (Old.stu_money-new.stu_money) drop trigger name_jiaoxuefei; create trigger Name_jiaoxuefei  After update on swpu_stu for each rowupdate swpu set money=money+ (Old.stu_money-new.stu_money); update swpu_stu set stu_money= Stu_money-1500 where id=1;    

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11

The event is insert? You cannot use the old ALTER TABLE SWPU add Stu_count int default 0;

CreateTrigger Name_kaixueafter insert on Swpu_stufor each row update swpu set stu_count = stu_count+ 1; drop trigger Name_kaixue;create trigger Name_kaixue after insert on swpu_stu for each row update swpu set stu_count = stu_count+new.id;     

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

The event is a delete? Cannot use new

create trigger name_biye after insert on swpu_stufor each rowdelete swpu set stu_count = stu_count-old.id;

• 1
• 2
• 3

If a trigger is composed of more than one SQL statement . should be satisfied as follows: 1. Statement block (begin end) is used to identify a block of statements. 2. Statement block statements require a separate statement terminator, semicolon.

Since the Terminator in a trigger program uses semicolons as a statement , it should be understood that when the command-line client encounters a semicolon, it should be interpreted as the end of the child statement within the trigger, rather than the end of the statement that created the trigger. Therefore, you should achieve the goal by modifying the command line's statement terminator. The delimiter statement can complete the SET statement terminator.

Don't forget to change it, then change it back.

DropTrigger Name_kaixue;delimiter $$CreateTrigger Name_kaixueAfterinsert on swpu_stufor each rowbegin Update Swpu set stu_count = Stu_count+1; update swpu set money = Money+100;< Span class= "Hljs-operator" >end$ $delimiter; insert into swpu_stu values ( Span class= "Hljs-number" >2,2000); select * from swpu;    

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14

Java enterprise-Class generic rights security framework source SPRINGMVC MyBatis or Hibernate+ehcache Shiro Druid Bootstrap HTML5

"Java Framework source code download"

MySQL transactions and triggers