在存储过程中自动填充列值，通过触发器调用 [英] autofilling column values in stored procedure, called via trigger

问题描述

当前，我有一堆执行相同操作的触发器。

Currently, I have bunch of triggers that do the same thing. This is copy-pasted for every table that needs this functionality.

delimiter $$

create trigger user_before_insert before insert on user for each row
begin
    set NEW.create_time = CURRENT_TIMESTAMP;
    set NEW.create_user_id = @user_id;
    set NEW.modify_time = CURRENT_TIMESTAMP;
    set NEW.modify_user_id = @user_id;
end$$

create trigger user_before_update before update on user for each row
begin
    set NEW.modify_time = CURRENT_TIMESTAMP;
    set NEW.modify_user_id = @user_id;
end$$

是否可以包装修改 OLD 和/或 NEW 到通过触发器调用的存储过程中？像这样的东西：

Is it possible to wrap the lines that modify OLD and/or NEW into stored procedures that are called via triggers? Something like this:

delimiter $$

create procedure autofill_on_insert(inout NEW data_type) -- what would be the data_type?
begin
    set NEW.create_time = CURRENT_TIMESTAMP;
    set NEW.create_user_id = @user_id;
    set NEW.modify_time = CURRENT_TIMESTAMP;
    set NEW.modify_user_id = @user_id;
end$$

create procedure autofill_on_update(inout NEW data_type) -- what would be the data_type?
begin
    set NEW.modify_time = CURRENT_TIMESTAMP;
    set NEW.modify_user_id = @user_id;
end$$

delimiter ;

create trigger user_before_insert before insert on user
    for each row call autofill_on_insert(NEW);
create trigger user_before_update before update on user
    for each row call autofill_on_update(NEW);

附加问题：如果可能，是否有任何方法可以检查 NEW 是否包含特定列？有些表没有 modify_time 和 modify_user_id 。

Additional question: if this is possible, is there any way to check if NEW contains specific columns? There are tables that do not have modify_time and modify_user_id.

推荐答案

我可以说不能将NEW传递到过程中，因为NEW是代表行的别名。过程的参数必须是标量值，例如INT，VARCHAR等。

I can say that NEW cannot be passed into the procedure, because NEW is an alias that represents a row. Procedure's arguments have to be scalar values, like INT, VARCHAR, etc..

关于 SET NEW.create_time = CURRENT_TIMESTAMP;；如果字段 create_time 是TIMESTAMP，则可以将CURRENT_TIMESTAMP设置为其默认值。

About the 'SET NEW.create_time = CURRENT_TIMESTAMP;'; if field create_time is a TIMESTAMP, you could set CURRENT_TIMESTAMP as default value for it.

这篇关于在存储过程中自动填充列值，通过触发器调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！