Trigger--mysql

Show TRIGGERS; View all triggers

Create Trigger TG1

After insert on user

For each row
Begin
Update user set Name= ' test ' where id=1;
End

Insert into User (Name,pass) VALUES (' Shao ', ' 23 ') $$

CREATE Trigger Syntax

CREATE TRIGGER trigger_name trigger_time trigger_event

on tbl_name  for each ROW  trigger_stmt

The trigger is related to a table named tbl_name .

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_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 a table

· UPDATE: Activating a trigger when a row is changed

· Delete: Activates the trigger when a row is deleted from the table

 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.

The MySQL database creation trigger is in the following format:

Create TRIGGER < Trigger name > {before | after} {Insert | update | delete} On < table name > For each row < trigger SQL statements >

Create trigger < trigger name;: Creates a new trigger and specifies the name of the trigger.
{before | after}: used to specify whether to fire before an insert, update, or DELETE statement is executed or after the statement executes.
On < table name: Used to specify the name of the table that should trigger the response.
For each row: The execution interval of the trigger, and the for each row notifies the trigger to perform an action every other row, rather than performing the entire table once.
< trigger SQL statement;: The SQL statement to be executed by the trigger, if the trigger is to execute multiple SQL statements, place multiple statements in the Begin...end block.

Instance:
(1): Create a data table under "Command Prompt" Test1
CREATE TABLE ' test1 ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar (not NULL)
)
(2): Re-create data table Test2
CREATE TABLE ' Test2 ' (
' num ' int (one) DEFAULT NULL,
)
(3): Create a Trigger
Create TRIGGER ' num ' before INSERT on ' test1 '
For each row update test2 set num = num + 1;
Insert data into the Test1 table, and num in the Test2 table adds a
(4): INSERT into Test1 (name) VALUES (' AA ');
(5): View data in the Test1 and Test2 tables

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.

DROP TRIGGER Test.num

Attention:

Using the old and new keywords, you can access the columns in the rows affected by the trigger (old and new are not case sensitive). In the Insert trigger, you can only use new. col_name, no old line. In the delete trigger, you can only use old. col_name, no new lines. In the update trigger, you can use old. Col_name to refer to the column of a row before the update, you can also use new. Col_name to refer to the columns in the updated row.

A column named with old is read-only. You can reference it, but you can't change it. For a column named with new, you can refer to it if you have SELECT permission. In the Before trigger, if you have update permissions, you can use the SET NEW. col_name = value "to change its value. This means that you can use the trigger to change the value that will be inserted into the new row, or to update the value of the row.

In the Before trigger, the new value of the Auto_increment column is 0, not the sequence number that will be automatically generated when a new record is actually inserted.

Insert into Usem (Id,name,pass) VALUES (2, ' Shao ', ' 23 ')

Create Trigger TAB1
After insert on Usem-----> cannot perform SQL operations on tables
For each row
Begin
Insert into User (Name,pass) VALUES (' Shao ', ' Chuan ');---> cannot have extra space keys
End

Trigger--mysql