MySQL Learning note-Trigger program

MySQL Learning note-Trigger program

A trigger is a named database object related to a table that fires when a specific event occurs on the table.

CREATE Trigger Syntax

CREATE Trigger Syntax:

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name    FOR EACH ROW    trigger_stmt

A trigger is a named database object related to a table that is activated when a specific event occurs on the table.

The trigger is related to a table named Tbl_name. Tbl_name must reference a persistent table. You cannot associate a trigger with a temporary table or view.

Trigger_time is the action time of the triggering program. It can be before or after to indicate that the trigger was triggered before or after the statement that activated it.

Trigger_event indicates the type of statement that activates the trigger. Trigger_event can be one of the following values:

1.

Insert data: Activates the trigger when inserting a new row into the table, for example, through the INSERT, LOAD data, and replace statements.

2.

Update data: Activates a trigger when a row is changed, for example, through an UPDATE statement.

3.

Delete data: Activates the trigger when a row is deleted from the table, for example, through the Delete and replace statements.

It is important that the trigger_event is not very similar to the SQL statement that activates the trigger as a table operation. For example, the before trigger on insert can be activated not only by the INSERT statement, but also by the load data statement.

LOAD DATA infile Statement Summary

The LOAD DATA infile statement is used to read rows from a text file at high speed and load a table. The file name must be a literal string.
Its syntax is:

‘file_name.txt‘    [REPLACE | IGNORE]    INTO TABLE tbl_name    [FIELDS        ‘string‘]        ‘char‘]        ‘char‘ ]    ]    [LINES        ‘string‘]        ‘string‘]    ]    [IGNORE number LINES]    [(col_name_or_user_var,...)]    [SET col_name = expr,...)]

If low_priority is used, execution of the load data statement is deferred until no other client is read from the table.

If a MyISAM table satisfies a condition that is inserted at the same time (that is, the table has free blocks in the middle), and the MyISAM table is assigned a concurrent (for multithreaded sharing), other threads retrieve the data from the table when load data is executing. Even if no other thread is using this table at the same time, using this option will slightly affect the performance of the load data.

If local is specified, it is considered to be related to the connected client:

• If local is specified, the file is read by clients on the client's host and sent to the server. The file is given a full path name to specify the exact location. If a relative path name is given, the name is understood to be the same as the directory where the client was started.

• If local is not specified, the file must be located on the server host and read directly by the server.

When locating files on a server host, the server uses the following rules:

• If an absolute path name is given, the server uses this path name.

• If a relative path name is given with one or more boot components, the server searches for files relative to the server data directory.

• Given a file name without a boot component, the server looks for files in the database directory of the default database.

Note that these rules imply that files named./myfile.txt are read from the server data directory, while the same files named MyFile.txt are read from the database directory of the default database.

If replace is specified, the input line replaces the original row (a row with the same value for a primary index or a unique index).

If ignore is specified, the input line that copies the original row to the unique key value is skipped. If neither of these options is specified, the operation depends on whether the local keyword is specified. When you do not use local, errors occur when duplicate keyword values occur, and the remaining text files are ignored. When using local, the default behavior is the same as when the ignore is specified, because in the middle of the run, the server has no way to abort the transfer of the file.

LOAD DATA infile is select ... into OutFile's complement. To write data to a file from a table, you should use SELECT ... into outfile statement. To read the file and put it back in the table, load DATA INFILE should be used.

The syntax for the fields and lines clauses is the same for two statements. Two clauses are optional, but if two are specified, fields must be in front of lines.

If you do not specify a fields clause, the default value is the value of the following statement:

BY‘\t‘BY‘‘BY‘\\‘

If you do not specify a lines clause, the default value is the value of the following statement:

BY‘\n‘BY‘‘

That is, when the input value is read, the default value causes the load DATA infile to run as follows:

1. Finds the boundary of a row at a new line.

2. Does not skip any row prefixes.

3. Breaks a row into a field at a tab.

4. You do not want the field to be included in any quotation mark characters.

5. When a tab appears, a new line, or a ' \ ' before ' \ ', it is understood as a literal character that is part of the field value.

Conversely, when the value is output, the default value causes the Select ... into outfile works as follows:

1. Write a tab tab between fields.

2. Do not include fields in any quotation-mark characters.

3. Use ' \ ' to escape when a tab, new line, or ' \ ' appears in the field value.

4. Writes a new line at the end of the row.

To write to the fields escaped by ' \ ', you must specify two backslashes for the value to be read, which is used as a SLR slash.

If you have generated a text file in your Windows system, you may have to use lines TERMINATED by ' \ r \ n ' to properly read the file because the Windows program typically uses two characters as a line terminator.

The IGNORE number lines option can be used to ignore rows at the beginning of a file. For example, you can use ignore 1 lines to skip a starting header row that contains column names, skipping the first row.

Cases:
First, I created a loadtest table in the database

The data in the Data.txt file under D disk is

In the Windows operating system, the Lines field is not added at the start of the load, and the default newline character is ' \ n ', which causes an error in reading the data

Adding the Lines field specifies that the newline character is ' \ r \ n ' and that the data can be loaded correctly.

Now continue to look at Trigger's grammar.

There cannot be two triggers for a given table with the same trigger action time and event. For example, you cannot have two before update triggers for a table. However, there can be 1 before update triggers and one before insert trigger, or 1 before update triggers and one after UPDATE trigger.

TRIGGER_STMT is the statement that executes when the triggering program is activated. If you plan to execute multiple statements, you can use the BEGIN ... End Compound statement structure.

Test

Create four tables First:

 CREATE TABLE test1 (A1 INT); CREATE TABLE test2 (A2 INT); CREATE TABLE test3 (A3 INT  not NULL auto_increment PRIMARY KEY); CREATE TABLE test4 (A4 INT  not NULL auto_increment  PRIMARY KEY, b4 INT DEFAULT 0);

Then define the trigger program, which interprets the trigger as
-Triggers the TESTREF program before inserting data into the Test1 table
-Insert data to Test2
-delete the same row of data in Test3 A3 as the new data inserted test1
-Update the data in the TEST4

DELIMITER | CREATE TRIGGER testref before INSERT  on test1  for each
       ROWBEGININSERT  into Test2 SET a2 = new.a1;            DELETE  from Test3 WHERE a3 = new.a1;       UPDATE test4 SET b4 = b4 + 1 WHERE a4 = new.a1;   END| DELIMITER;

Using the alias old and new, you can refer to the columns in the table related to the triggering program. Old.col_name refers to 1 columns in an existing row before updating or deleting it. New.col_name references the 1 column or 1 columns of the row that will be inserted after it has been updated.
When the trigger is activated, the SELECT permission is required for all old and new columns that the trigger references, and the update permission is required for all new columns that are the target of the set assignment.

Next, insert some pre-data into the TEST3 and TEST4 tables first

 INSERT  into Test3 (A3) VALUES (null), (NULL), (NULL), (  Null), (null), (NULL), (null), (empty), (null), (  NULL); INSERT  into test4 (A4) VALUES (0), (0), (0), (0), ( C8>0), (0), (0), (0), (0), (0);

Then start inserting data into the Test1

INSERT INTO test1 VALUES     (1), (3), (1), (7), (1), (8), (4), (4);

Finally, the data in each table can be found as follows:

DROP Trigger Syntax

DROP Trigger Syntax

DROP TRIGGER [schema_name.]trigger_name

Discard the triggering program. The scheme name (schema_name) is optional. If schema is omitted, the trigger is discarded from the current scenario.

How errors are handled during the execution of the triggering procedure

During the execution of the trigger, MySQL handles the error in the following way:

• If the before trigger fails, the action on the corresponding line is not performed.

• The after trigger is executed only if the before trigger (if any) and the row operation have been executed successfully.

• If an error occurs during the execution of the before or after trigger, the failure of the entire statement that invokes the trigger is caused.

• For transactional tables, any changes that the statement makes will be rolled back if the trigger fails (and the resulting failure of the entire statement). For non-transactional tables, this type of rollback cannot be performed, so any changes made before the failure are valid even if the statement fails.

MySQL Learning note-Trigger program