My MySQL Learning experience (12)

Original: My MySQL learning experience (12)

My MySQL Learning experience (12)

My MySQL learning experience (a)

My MySQL learning Experience (ii)

My MySQL learning Experience (iii)

My MySQL learning experience (iv)

My MySQL learning experience (v)

My MySQL learning experience (vi)

My MySQL learning experience (vii)

My MySQL learning experience (eight)

My MySQL learning experience (ix)

My MySQL learning experience (10)

My MySQL learning experience (11)

This article "My MySQL Learning Experience (ii)" will explain the MySQL trigger

A trigger is a special stored procedure, in which the stored procedure is called with call, and the trigger does not need to use the calling

It does not need to be started manually, as long as a predefined event occurs, it is automatically called by MySQL.

Create a Trigger

The syntax is as follows:

CREATE TRIGGER trigger_name trigger_time trigger_event      on  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 staging 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:

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

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

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

It is important to note that 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.

One example of possible confusion is insert into. On DUPLICATE UPDATE ... Syntax: The before INSERT trigger is activated for each row, followed by the after insert trigger, or before update and after update triggers, depending on whether there are duplicate keys on the row.

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. This allows you to use the same statements that are allowed in the stored subroutine.

Create a trigger for a single execution statement

CREATE TABLE INT DECIMAL (ten,2)); CREATE TRIGGER INSERT  on  Account  for SET @SUM = @SUM +New.amount;

First, create an Account table with two fields in the table: Acct_num field (defined as int type)

Amount field (defined as floating-point type), followed by a trigger named Ins_sum, which triggers the condition that the data table account is inserted before the data is

To sum the newly inserted amount field values

DECLARE @num INT SET @num = 0 INSERT  into VALUES (1,1.00), (2,2.00)SELECT@num

First, create an account table that calculates the sum of the amount values for all newly-inserted calculation tables before inserting the data into the table.

The name of the trigger is Ins_num, which is triggered before the data is inserted into the table.

Create a trigger with more than one execution statement, with the following syntax:

DELIMITER| CREATE TRIGGERTestref beforeINSERT  ontest1 forEach ROWBEGIN    INSERT  intoTest2SETA2=new.a1; DELETE  fromTest3WHEREA3=new.a1; UPDATETest4SETB4=B4+ 1 WHEREA4=new.a1; END|

View triggers

Viewing a trigger is the definition, status, syntax information, and so on, of a trigger that already exists in the database.

You can use show TRIGGERS and view trigger information in the TRIGGERS table

 trigger  Event table  Statement Timing Created sql_mode definer character_set_client collation_connection database   Collation  -- -----  ------  -------  ------------------------  ------  -------  --------  --------------  --------------------  ------ -------------------------------- ins_sum insert  account set   @sum  =   @sum  +  New.amount before (null ) Root
   
     @localhost  UTF8 utf8_general_ci utf8_general_ci 
   
 

The event represents the activity that activates the trigger, where the trigger event is the insert Operation insert,table the Object table that represents the activation trigger, which is the account table

Timing represents the time of the trigger, before insertion (before), Statement represents the action performed by the trigger, and some other information, such as SQL schema, trigger definition account and character set, etc.

Viewing trigger information in the Triggers table

INFORMATION_SCHEMA database in the Triggers table, you can view the trigger information through the query

SELECT *  from WHERE ' trigger_name '='ins_sum'

trigger_catalog trigger_schema trigger_name event_manipulation event_object_catalog EVENT_OBJECT_SCHEMA EVENT_OB Ject_table action_order action_condition action_statement action_orientation action_timing ACTION_REFERENCE_         Old_table action_reference_new_table action_reference_old_row action_reference_new_row CREATED SQL_MODE DEFINER Character_set_client collation_connection database_collation---------------  --------------  ------------  ------------------  --------------------  -------------------  ---------- --------  ------------  ----------------  ------------------------  ------------------  -------------  -------------------   -------  --------------------------  ------------------------  ------------------------  -------  --------  -------------- --------------------  --------------------  ------------------DEF school Ins_sumINSERTDEF School Account0(NULL)Set @sum=@sum+New.amount ROW before (NULL)                      (NULL) Old NEW (NULL) root@localhostUTF8 Utf8_general_ci utf8_general_ci

Trigger_schema represents the database where the trigger resides

Trigger_name indicates the name of the trigger

Event_object_table indicates on which table the trigger

Action_statement represents the specific action that is performed when the trigger is triggered

Action_orientation is row, which indicates that it is triggered on each record

Action_timing indicates that the moment of triggering is before

Delete Trigger

Use the Drop TRIGGER statement to delete a trigger that has already been defined in MySQL and delete the basic syntax of the trigger

DROP TRIGGER [schema_name. ]trigger_name

where (schema_name) is optional

If schema is omitted, the trigger is discarded from the current scenario.

Delete Ins_sum Trigger

DROP TRIGGER ' school '. ' Ins_sum '

Trigger Ins_sum Delete Succeeded

Summarize

For the same table, you can create only one trigger for the same event, such as creating a before insert trigger on the table account

Then if you create a before insert trigger on the table account again, MySQL will error, at this point, only on the table account

Create a trigger for after insert or before update type

If there is a wrong place, welcome everyone to shoot brick O (∩_∩) o

2014-6-23 Supplement

CREATE TABLEAccount (Acct_numINT, amountDECIMAL(Ten,2), NUMINT );CREATE TRIGGERIns_sum beforeINSERT  onAccount forEach ROWUPDATE' Employee 'SET' age '=New.acct_num+ 1 WHERE' ID '=New.acct_num;INSERT  intoAccount (Acct_num,amount)VALUES(1,2.00);SELECT *  from' Employee '