Explain the usage of MySQL stored procedures and triggers

Test Table Structure:

CREATE TABLE `a` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `cnt` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT

Several permissions related to stored procedures:

  alter routine,create routine,execute   

Permissions related to triggers:

  trigger  

Create a test account:

grant select,update,insert,create,delete,trigger,alter routine,create routine,execute on test.* to 'mysqldba'@'%' identified by '123456';

Usage:

1. The stored procedure is defined by the caller and the trigger has

We can specify the definer and caller for a stored procedure. If there is a definer in the wood, it is the Creator's environment by default, but the trigger does not work. Once a trigger is created, all operations performed by users are triggered.

For example:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`p_test`$$

CREATE DEFINER=`mysqldba`@`%` PROCEDURE `p_test`(in a int)
BEGIN
select a;
END$$

DELIMITER ;

Only the mysqldba account can be called. However, accounts with higher permissions, such as root, can also be called.

2. The trigger can call the stored procedure.

3. the trigger can access the new and old data of its own table (through the old. colname, new. can also affect other tables, but cannot modify tables that have been used by functions or triggers (read or write). In versions earlier than mysql5.0.10, the trigger cannot modify other tables.

4. in MySQL, all triggers are based on rows (for each row). Because triggers are activated through insert, update, and delete, mysql5.0 does not support for each statement, for example:

Delimiter $

Drop trigger /*! 50032 if exists */'test'. 'tai _ a' $

Create
/*! 50017 definer = 'mysqldb' @ '% '*/
Trigger 'tai _ a' after insert on 'A'
For each row -- MySQL 5.0 can only do this, not for each statement
Begin
Insert into B values (New. ID, new. CNT );
End;
$

Delimiter;

5. triggers can call external applications through udfs

For details, refer to: http://forge.mysql.com/projects/project.php? Id = 211

Internal:

6. We can view all the triggers under a database in the following way:

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='dbname';

7. Storage of triggers

Currently, the trigger is stored in the. Trg file. The trigger in each table is stored in a Trg file. We can see this file in the table's data directory, for example:

[root@sunss test]# ls
a.frm  a.MYD  a.MYI  a.TRG  b.frm  b.MYD  b.MYI  tai_a.TRN  tau_a.TRN  test.frm  test.MYD  test.MYI
[root@sunss test]# pwd
/home/mysql/test
[root@sunss test]# cat a.TRG 
TYPE=TRIGGERS
triggers='CREATE DEFINER=`mysqldba`@`%` TRIGGER `tau_a` AFTER UPDATE ON `a` \n    FOR EACH ROW BEGIN\n            update b set cnt=NEW.cnt where id=NEW.id;\n    END' 'CREATE DEFINER=`mysqldba`@`%` TRIGGER `tai_a` AFTER INSERT ON `a` \n    FOR EACH ROW BEGIN\n         insert into b values(NEW.id,NEW.cnt);            \n    END'
sql_modes=0 0
definers='mysqldba@%' 'mysqldba@%'
client_cs_names='utf8' 'utf8'
connection_cl_names='utf8_general_ci' 'utf8_general_ci'
db_cl_names='utf8_general_ci' 'utf8_general_ci'
You have new mail in /var/spool/mail/root
[root@sunss test]#

8. triggers can update remote tables, but remote tables must be used.Federated storage engine

Replication related:

9. triggers can also work in the MySQL replication environment.

In mysql5.0, triggers and replication work like most other database systems. Operations carried by triggers on the master database are not copied to the slave database. However, if a trigger exists on the master database and is also created in a table related to the slave database, the trigger on the slave database can be activated and triggered like the master database.

10. How are operations performed on the master database copied to the slave database?

First, make sure that the master database has the same trigger. The trigger created on the master database must be re-created on the slave database. In this way, the DML statement can activate the trigger on the slave database during replication. For example, in table A, we create an after insert trigger in Table A. The replication process is as follows:

1) insert an insert statement into Table

2). After insert activates the trigger and is also inserted to table B.

3) The insert statement is written to the bin log.

4). The replication thread obtains and executes the insert statement.

5). After insert activates the trigger and is also inserted to table B.

Reference: Permission Control for stored procedures and views in the MySQL Manual