Mysql trigger and mysqltrigger

Mysql trigger and mysqltrigger

Trigger: monitors a situation and triggers an operation.

Four elements of trigger creation Syntax: 1. Monitoring location (table) 2. Monitoring event (insert/update/delete) 3. trigger time (after/before) 4. trigger event

Syntax:

CREATE TRIGGER trigger_name trigger_time trigger_event    ON tbl_name FOR EACH ROW trigger_stmt

The trigger program is related to the table named tbl_name. Tbl_name must reference a permanent table. The trigger program cannot be associated with a temporary table or view.

Trigger_time is the time when the program is triggered. It can be before or after to indicate that the trigger program is triggered before or after its statement is activated.

Trigger_event indicates the type of statements used to activate the trigger program. Trigger_event can be one of the following values:

Insert: the trigger program is activated when a new row is inserted into the table, for example, through insert, load data, and replace statements.

Update: The trigger program is activated when a row is changed, for example, through the update statement.

Delete: the trigger program is activated 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 similar to the SQL statement used to activate the trigger program in the form of table operations.

For example, the before trigger program for insert can not only be activated by the insert statement, but also be loadDataStatement activation.

Create trigger triggerName

After/before insert/update/delete on table name

For each row # This statement is fixed in mysql.

Begin

SQL statement;

End;

 

ForInsertStatement. Only new statements are valid;

ForDeleteStatement. Only old is valid;

ForUpdateStatement, which can be used by both new and old.

 

Create a table (two tables to be operated by the trigger)

/* Auto_increment: auto-increment; priamry key: primary key; comment: comment */

/* Drop: Delete; if exists xxx (determines whether the xxx name exists in the Database )*/

/* For each row: The execution data of one row in a loop */

/* After insert/update/delete on table_name: The table to which the insert/update/delete operation is performed */

Drop table if exists table1; create table table1 (id int (4) primary key auto_increment not null comment 'id', name varchar (225) comment 'name '); drop table if exists table2; create table table2 (id int primary key auto_increment not null comment 'id', name varchar (225) comment 'name ');

Difference between Before and After:

Before: (insert and update) You can modify new. after, you cannot modify new. Both of them cannot modify old data.

Insert trigger

drop trigger if exists insert_on_table1;create trigger insert_on_table1after insert  on table1for each rowbegininsert into table2(name) value(new.name);end

Action trigger

insert table1(name) value('aaa');

Check whether Table 2 has a value.

select * from table2;

Delete trigger

drop trigger if exists delete_on_table1;create trigger delete_on_table1after delete on table1for each ROWbegindelete from table2 where name=old.name;end

Delete

delete from table1 where id=1;

Query Table 2 Changes

select * from table2;

Update table1 update trigger

drop trigger if exists update_on_table1;create trigger update_on_table1after update on table1for each ROWbeginupdate table2 set name=new.name where name=old.name;end

Update

update table1 set name='ccc';

Query Table 2 Changes

select * from table2;

Example of using before statistics to insert points:

Create a table

Drop table if exists table3; create table table3 (id int primary key auto_increment comment 'id', num int comment 'credits ') engine = myisam default charset = utf8 comment = 'standalone tabulation ';

Create a trigger received with function variables

drop trigger if exists insert_on_table3;create trigger insert_on_table3before insert on table3for each row set @sum=@sum+new.num;

Execute trigger

set @sum=0;insert into table3 values(1,2),(2,3),(3,3),(4,3);select @sum;