Shulou(Shulou.com)05/31 Report--

This article mainly introduces the difference between before and after in mysql, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

Trigger (trigger): monitors a situation and triggers an action. It is a method provided to programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events. Its execution is not called by the program or started manually, but is triggered by events, such as activating it when an operation is performed on a table (insert,delete, update).

Triggers are often used to strengthen data integrity constraints and business rules. Four elements of trigger creation syntax:

1. Surveillance location (table)

two。 Monitoring events (insert/update/delete)

3. Trigger time (after/before)

4. Trigger event (insert/update/delete)

Where: trigger_time is the trigger event of the trigger, which can be before (triggered before checking the constraint) or after (triggered after checking the constraint); trigger_event is the trigger event of the trigger, including insert, update and delete. You can use old and new to refer to the changed records in the trigger.

What to pay attention to:

1) it should be noted that for the same trigger event (insert/update/delete) with the same trigger time (after/before) of the same table, only one trigger can be defined, otherwise an error is reported.

ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

2) the syntax format of if judgment in mysql trigger is as follows: (1). There can also be loops in the loop, (2) .else is not followed by then, (3). Else if there is a loop! Not else if!

If...then {

If...then {}

End if

If...then {}

End if

...

}

Elseif...then..

Else

End if

Note that you can use two if loops, each end if.

3) notice the difference between before and after in mysql triggers:

Before: (insert, update) you can modify new

After: new cannot be modified, and neither of them can modify old data.

For INSERT statements, only NEW is legal

For DELETE statements, only OLD is legal

For UPDATE statements, NEW and OLD can be used simultaneously.

After first completes the addition, deletion and modification of the data, and then triggers. The triggered statement is later than the monitored add, delete, delete and change operation, so it cannot affect the previous add, delete, delete and change action; that is to say, insert the order record first, and then update the quantity of goods.

Before first completes the trigger, then adds, deletes and modifies, and the triggered statements precede the monitored additions and deletions, so that the new can be modified.

An example from the network illustrates:

First, let's create two tables:

# goods list

Create table g

(

Id int primary key auto_increment

Name varchar (20)

Num int

);

# order form

Create table o

(

Oid int primary key auto_increment

Gid int

Much int

);

Insert into g (name,num) values ('Commodity 1, commodity 10), (' Commodity 2, commodity 10), ('Commodity 3, commodity 10)

After we place an order with the help of triggers, we automatically subtract the corresponding items in the commodity list, as follows:

Create trigger tg2

After insert on o

For each row

Begin

Update g set num=num-new.much where id=new.gid

End$

But there is a problem that if the number of orders exceeds the total number of goods, it will lead to a negative number in the commodity table, so we can use before to modify the new value in the order to ensure that there will not be a negative number in the goods table.

Case: when adding an order record, judge the quantity of goods in the order. If the quantity is greater than 10, it is changed to 10 by default.

DELIMITER $

Create trigger tg6

Beforeinsert on o

For each row

Begin

If new.much > 10 then

Set new.much = 10

End if

Update g set num = num-new.much where id = new.gid

End $

DELIMITER

4) this is not to say that a transaction starts once. If the following transaction modifies 10 rows, it will trigger 10 times:

Mysql > update blocks_infos set infos_id=1 where infos_id=2

Query OK, 10 rows affected (0.22 sec)

Rows matched: 10 Changed: 10 Warnings: 0

5) in the case of before, if the triggered operation is not successful, the original trigger event will not succeed.

Next, record the case I wrote. When one table is added, deleted or modified, it triggers the corresponding action on another table.

For example, if there is a syntax error or execution error after the begin, it will cause the previous delete to fail

DELIMITER $

Create trigger tri_delete_blocks_infos1 before delete

On blocks_infos for each row

Begin

DECLARE h int

Set h = (select intc from bidinfo.v_publish_info where id=old.infos_id)

If h is null then

Update bidinfo.v_publish_info set intc=1 where id= old.infos_id

Else

Update bidinfo.v_publish_info set intc=intc+1 where id= old.infos_id

End if

End $

DELIMITER

1. About triggers for insert:

Our requirement is to determine whether there is an infos_id of the new insert in the blocks_infos_ open search table when going to blocks_infos. If so, the corresponding update, and if not, insert. You can do it in the following two ways:

Method 1 uses replace:

DELIMITER $

Create trigger tri_insert_blocks_infos after insert

On blocks_infos for each row

Begin

Replace into blocks_infos_opensearch (infos_id,blocks) select infos_id,group_concat (blocks_id) blocks from blocks_infos where infos_id=new.infos_id group by infos_id

End $

DELIMITER

Note that there are three forms of MySQL replace into (the into keyword can be omitted):

1. Replace into tbl_name (col_name,...) Values (...)

2. Replace into tbl_name (col_name,...) Select...

3. Replace into tbl_name set col_name=value,...

Method 2: use if to judge:

DELIMITER $

Create trigger tri_insert_blocks_infos after insert

On blocks_infos for each row

Begin

DECLARE c INT

Set c = (SELECT COUNT (infos_id) FROM blocks_infos WHERE infos_id=new.infos_id)

If Centr1 then

Insert into blocks_infos_opensearch select infos_id,GROUP_CONCAT (blocks_id) blocks FROM blocks_infos WHERE infos_id=new.infos_id

Elseif c > 1 then

UPDATE blocks_infos_opensearch SET blocks= (SELECT GROUP_CONCAT (blocks_id) blocks FROM blocks_infos WHERE infos_id=new.infos_id) WHERE infos_id=new.infos_id

End if

End $

DELIMITER

two。 About triggers for delete:

DELIMITER $

CREATE TRIGGER tri_delete_blocks_infos after DELETE

ON blocks_infos FOR EACH ROW

BEGIN

DECLARE c INT

SET c = (SELECT COUNT (infos_id) FROM blocks_infos WHERE infos_id=old.infos_id)

IF cymb0 THEN

DELETE FROM blocks_infos_opensearch WHERE infos_id=old.infos_id

ELSEIF c > 0 THEN

UPDATE blocks_infos_opensearch SET blocks= (SELECT GROUP_CONCAT (blocks_id) blocks FROM blocks_infos WHERE infos_id=old.infos_id) WHERE infos_id=old.infos_id

END IF

END $

DELIMITER

3. About triggers for update:

DELIMITER $

CREATE TRIGGER tri_update_blocks_infos after update

ON blocks_infos FOR EACH ROW

BEGIN

DECLARE c INT

DECLARE d varchar (1000)

DECLARE h varchar (1000)

SET c = (SELECT COUNT (infos_id) FROM blocks_infos WHERE infos_id=old.infos_id)

Set d = (SELECT GROUP_CONCAT (blocks_id) blocks FROM blocks_infos WHERE infos_id=old.infos_id)

Set h = (SELECT GROUP_CONCAT (blocks_id) blocks FROM blocks_infos WHERE infos_id=new.infos_id)

IF cymb0 THEN

DELETE FROM blocks_infos_opensearch WHERE infos_id=old.infos_id

ELSEIF c > 0 THEN

UPDATE blocks_infos_opensearch SET blocks= d WHERE infos_id= old.infos_id

UPDATE blocks_infos_opensearch SET blocks= h WHERE infos_id= new.infos_id

END IF

END $

DELIMITER

Another requirement is that when the table blocks_infos is processed, another table bidinfo.v_publish_info will be triggered to do the corresponding processing, because the after insert on blocks_infos has already been established and the after insert can no longer be established.

On blocks_infos, so you can only create before insert on blocks_infos. Three are created as follows:

1) insert

DELIMITER $

Create trigger tri_insert_blocks_infos1 before insert

On blocks_infos for each row

Begin

DECLARE d int

Set d = (select intc from bidinfo.v_publish_info where id=new.infos_id)

If d is null then

Update bidinfo.v_publish_info set intc=1 where id= new.infos_id

Else

Update bidinfo.v_publish_info set intc=intc+1 where id= new.infos_id

End if

End $

DELIMITER

2) delete

DELIMITER $

Create trigger tri_delete_blocks_infos1 before delete

On blocks_infos for each row

Begin

DECLARE h int

Set h = (select intc from bidinfo.v_publish_info where id=old.infos_id)

If h is null then

Update bidinfo.v_publish_info set intc=1 where id= old.infos_id

Else

Update bidinfo.v_publish_info set intc=intc+1 where id= old.infos_id

End if

End $

DELIMITER

3) update, note that you can use only two if loops!

DELIMITER $

Create trigger tri_update_blocks_infos1 before update

On blocks_infos for each row

Begin

DECLARE j int

DECLARE i int

Set I = (select intc from bidinfo.v_publish_info where id=new.infos_id)

Set j = (select intc from bidinfo.v_publish_info where id=old.infos_id)

If j is null then

Update bidinfo.v_publish_info set intc=1 where id= old.infos_id

Else

Update bidinfo.v_publish_info set intc=intc+1 where id= old.infos_id

End if

If i is null then

Update bidinfo.v_publish_info set intc=1 where id= new.infos_id

Else

Update bidinfo.v_publish_info set intc=intc+1 where id= new.infos_id

End if

End $

DELIMITER

Thank you for reading this article carefully. I hope the article "what is the difference between before and after in mysql" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.