MySQL trigger Learning

1. A simple example

1.1. Create a table:

Create Table T (S1 integer );

1.2. Trigger:

delimiter |create trigger t_trigger before insert on t  for each row begin set @x = "hello trigger";       set NEW.s1 = 55;end; |

1.3. If a trigger creation error occurs, the trigger can only be deleted. At least I have tried replace.

Drop trigger t_trigger;

1.4. When insert is executed:

Insert into T values (1 );

1.5. The trigger t_trigger will be executed

Select @ x, t. * from T;

1.6. The result is displayed:

1.7 you can use show triggers; to view the newly created trigger.

2. Maintenance trigger for URL query Hash Value

2.1 create a table pseudo hash.

2.2 create a trigger. When a table is inserted or updated, the trigger is triggered.

delimiter |
create trigger pseudohash_crc_ins before insert on pseudohash  for each row 
begin set @x = "hello trigger";
set NEW.url_crc=crc32(NEW.url);
end; 
|
create trigger pseudohash_crc_upd before update on pseudohash  for each row 
begin set @x = "hello trigger";
set NEW.url_crc=crc32(NEW.url);
end; 
|

delimiter ;

2.3 Insert operation

Insert into pseudo Hash (URL) values ("http://www.baidu.com ");

Insert into pseudo Hash (URL) values ("http://www.163.com ");

2.4 view the data in the table (the data after the update operation)

2.5 update

Update pseudo hash set url = 'www .163.com 'Where id = 1;

We can see that after the insert and update operations, their url_crc is different.

----------------------------------------------------------------------------

2.6 The above is an example of creating an index for a URL. There is also a way to create an index: Create a pseudo index on the B + tree, which is different from the real index, it still searches on the B + tree index. However, it uses the hash value of the key instead of the Key itself, which will speed up the search.

2.6.1 create an URLs table. Note that the memory storage engine is used.

CREATE TABLE `urls` (
  `url` varchar(255) DEFAULT NULL,
  `url_crc` int(11) DEFAULT '0',
KEY `url` (`url`) USING HASH
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

2.6.2 then insert the URL and url_crc, for example

Insert into URLs values ('www .gougou.com ', CRC32 ('www .gougou.com '));

As shown above, or use a trigger

2.6.3 use Hash index for query

Select * From URLs where url = "www.baidu.com" and url_crc = CRC32 ("www.baidu.com ");

Select * From URLs where url_crc = CRC32 ("www.baidu.com ");

Select * From URLs where url = "www.baidu.com"

The above three query results are of course the same, but the hash speed is much faster.

3. Trigger syntax

3.1 create trigger trigger_name trigger_time trigger_event

              ON tbl_name FOR EACH ROW trigger_stmt

Trigger_time is the time when the program is triggered. It can be before or aftertrigger_event that specifies the type of statement 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.

3.2 possible problems

If you insert/update the data you just inserted in the trigger, it will cause a loop call.

For example:

Create trigger test before update on test for each row update test set new. updatetime = now () Where id = new. ID; End

Set should be used:

Create trigger test before update on test for each row set new. updatetime = now (); End

3.3 trigger and stored procedure

The trigger program cannot call the storage program that returns data to the client, nor use dynamic SQL statements that use the call statement.

(Allows the storage program to return data to trigger programs through parameters ).

The stored procedure can accept parameters and give the results to the application.