The difference between mysql stored procedures and triggers

Mysql stored procedure is a set of SQL statements to complete specific functions in a large database system; triggers are a special type of stored procedures; triggers are different from stored procedures, and triggers are mainly It is executed by triggering events, and stored procedures can be called directly through the stored procedure name.

# Stored procedure: It is a set of SQL statements designed to complete specific functions in a large database system. Trigger: Trigger is a special type of stored procedure, which is different from stored procedure. Trigger is mainly triggered by events and executed, while stored procedure can be called directly through the stored procedure name.

Stored procedure:
is in a large database system,
a set of SQL statements to complete specific functions,
is stored in the database, and after the first There is no need to compile again when calling again after the first compilation.
The user executes it by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters).
Stored procedure is an important object in the database

Advantages:
1 allows modular programming (create once and use multiple times)
2 allows faster execution
3 reduces network Traffic
4 Better security mechanism

Format:

DELIMITER // 
CREATE PROCEDURE 储存名([ IN ,OUT ,INOUT ]?参数名?数据类形...) 
BEGIN 
SQL语句 
END // 
DELIMITER ;

Calling process:

Use call process name ()

View all storage Process show procedure status;
View the created stored procedure show create procedure process name;
Delete the process drop procedure process name

In means that the parameters are passed in from the outside and used inside (used inside the process)
Out means that the parameter saves the data into the variable from the process and gives it to the outside for use. All passed in must be variables. If the incoming out variable itself has data outside, then after entering the process, the first thing It is cleared and set to null
Inout data can be passed in from the outside to be used inside the process. At the same time, after the internal operation, the data will be returned to the outside

trigger Trigger:

Trigger is a special type of stored procedure, which is different from stored procedure.
Trigger is mainly triggered by events and executed, while stored procedures can be executed through The stored procedure name is called directly.

Function:
1. Can force verification or conversion of data before writing to the data table
2. When an error occurs in the trigger, the result of the change will be undone

Format

DELIMITER //
Create trigger --触发器名字 触发时机 触发事件 on 表 for each 
row 
Begin 
--操作的内容 
End // 
DELIMITER ;

Trigger object: on table for each row Trigger binding is essentially all rows in the table, so when each row changes, the trigger will be triggered
Trigger timing: corresponding to each table The rows will have different states. When the SQL command occurs,
will cause the data in the row to change. Each row will always have two states. Before operating data (before), after operating data (after)
Trigger events:
The target of triggers in Mysql is changes in data, and the corresponding operations are only (add, delete, modify) queries that do not generate data Change,
So the query does not trigger events
Notes:
In a table, there can only be one trigger type corresponding to the trigger event bound to each trigger timing;
There can only be one in a table An after insert trigger. Therefore, the maximum number of triggers in a table can only be six

Create stored procedures

DELIMITER //
CREATE PROCEDURE addUser
(IN uCode VARCHAR(50),IN uName VARCHAR(20),IN uRole INT,IN sex INT,IN tel VARCHAR(30))
BEGIN
INSERT INTO smbms_user (userCode,userName,userRole,gender,phone)
VALUES(uCode,uName,uRole,sex,tel);
END//
DELIMITER //
查看存储过程 show procedure status;
<insert id="saveUser">
CALL addUser(#{userCode},#{userName},#{userRole},#{gender},#{phone})
</insert>
public int saveUser(
@Param("userCode") String userCode,
@Param("userName") String userName,
@Param("userRole") Integer userRole,
@Param("gender") Integer gender,
@Param("phone") String phone);
public List<User> findUserListPage(String queryUserName, 
Integer queryUserRole, 
Integer currentPageNo, Integer pageSzie);

public boolean saveUser(String userCode, String userName, Integer userRole,
Integer gender, String phone) {
SqlSession sqlSession = null;
int row = 0; // 受影响的行数
try {
sqlSession = MyBatisUtil.createSqlSession();
row = sqlSession.getMapper(UserMapper.class).saveUser(userCode, userName, userRole, gender, phone);
// 提交事务
sqlSession.commit();
} catch (Exception e) {
if (sqlSession != null) {
sqlSession.rollback();
}
row = 0;
e.printStackTrace();
} finally {
MyBatisUtil.closeSqlSession(sqlSession);
}
if (row > 0) {
return true;
}
return false;
}

userService.saveUser("zhangcuishan", "亚索", 1, 2, "15645678941");

Create triggers

创建两张表
create table my_goods(
id int primary key auto_increment,
name varchar(20) not null,
inv int
)
create table my_orders(
id int primary key auto_increment,
goods_id int not null,
goods_num int not null)

insert into my_goods values(null,'手机',1000),(null,'电脑',500),(null,'游戏机',100);

DELIMITER //
CREATE TRIGGER a_i_o_t AFTER INSERT ON my_orders FOR EACH ROW
BEGIN
UPDATE my_goods SET inv =inv -new.goods_num WHERE id=new.goods_id;
END
//
DELIMITER ;

DELIMITER //
CREATE TRIGGER b_i_o_t BEFORE INSERT ON my_orders FOR EACH ROW 
BEGIN 
SELECT inv FROM my_goods WHERE id=new.goods_id INTO @inv;
IF @inv <new.goods_num THEN 
INSERT INTO xxx VALUES('xx');
END IF;
END 
//
DELIMITER //
 

测试 insert into my_orders values(null,3,5);

The above is the detailed content of The difference between mysql stored procedures and triggers. For more information, please follow other related articles on the PHP Chinese website!