"MySQL must know" reading notes-triggers and management transaction processing

Trigger

A trigger is a MySQL statement that is automatically executed by MySQL in response to Delete,insert,update, and other statements do not support triggers.

When creating a trigger, 4 conditions are required;
Unique Trigger Name
The table associated with the trigger
The activity that the trigger should respond to
When triggers are executed

The trigger names in MySQL must be unique in each table, but two tables in the same database can have triggers with the same name. This condition is not allowed in other DBMS, so for the sake of unification, it is best to set the trigger name to be unique in the same database.

Create a Trigger

CREATE TRIGGER INSERT  on  Products  for SELECT ' Product added ' ; CREATE TRIGGER -- create a new trigger called Newproduct INSERT -- this trigger will execute after the INSERT statement succeeds  for Each row- code executes on each insert row

Attention:

Only tables can support triggers, and views, temporary tables are not supported
Each table supports a maximum of 6 triggers. A single trigger cannot be associated with multiple events or multiple tables.
If the before trigger fails, MySQL does not perform the request operation.
If the before trigger or statement itself fails, MySQL will not execute after trigger

Delete Trigger

DROP TRIGGER newproduct;

Triggers cannot be modified or overwritten, and must be deleted and then created if they are to be modified.

Using triggers

Insert Trigger
You can refer to a virtual table called new to access the inserted row
Delete Trigger
Referencing a virtual table called old to access the deleted row
UPDATE trigger
The missing pages in the book are not introduced

Manage transaction processing

There are several kinds of engines in MySQL, but not every engine supports transaction processing
Common engine InnoDB support transactions, MyISAM does not support transactions
Several nouns:
Transaction transaction
Fallback rollback
Commit Commit
Reserved Point SavePoint

Control transaction Management

ROLLBACK SELECT *  from  TRANSACTION; DELETE  from ordertotals; SELECT *  from ordertotals; ROLLBACK ; SELECT *  from OrderTotals;

Note: Transactions are used to manage insert, update, and delete. Cannot fallback create or drop operation

COMMIT START tansaction; DELETE  from WHERE = 20010 ; DELETE  from Order WHERE = 20010 ; COMMIT;

Reserved points

-- Create a retention point savepoint delete1; -- Roll back to a retention point ROLLBACK  to Delete1;

The retention point is then executed automatically after the rollback or commit, and after MySQL 5, you can explicitly release the retention point with release savepoint

Change the default commit behavior

SET autocommit=0

Note: The AUTOCOMMIT flag is for each connection, not the server;

"MySQL must know" reading notes-triggers and management transaction processing