mysql - Trigger to Insert N number of default values into row

Question

So I have created the following Trigger:

CREATE TRIGGER cascadeUserInsertionToRecommendations_tbl
    -> AFTER INSERT
    -> ON users_tbl FOR EACH ROW
    -> INSERT INTO recommendations_tbl(recommendation_userid, recommendation_category, recommendation_manufacturer) VALUES(NEW.user_id, 'diverse', 'diverse');
Query OK, 0 rows affected (0.027 sec)

Actually I could have used the Default() instead of "diverse" on the other fields because "diverse" is actually the default value of these columns. But thats not the problem here.

In case I expand this table, I would very much appreciate if the above Trigger dynamically adapted to the new situation. Basically, the Trigger just needs to fill ALL the fields of the new row with the default value except for the one receiving the NEW.user_id.

I wondered if MySQL provided some syntax to accomplish this?

标签： mysqlsqldatabasesql-insertdatabase-trigger

Answer 1

基本上，触发器只需要用默认值填充新行的所有字段，除了接收NEW.user_id.

假设您为 excepted 的每一列正确定义了默认值recommendation_userid，您所要做的就是在插入时不传递这些额外的列。MySQL 会自动为插入时未提供的列分配默认值。

所以你的INSERT命令应该看起来像：

insert into recommendations_tbl(recommendation_userid) values(EW.user_id);

即使在目标表中创建了新列，这也将继续工作（再次假设这些新列具有正确定义的默认值）。

DB Fiddle 上的演示：

-- create the table with default values for all columns excepted the primary key
create table recommendations_tbl (
    recommendation_userid integer primary key, 
    recommendation_category varchar(10) default 'diverse',
    recommendation_manufacturer varchar(10) default 'diverse'
);


-- insert a record, providing the primary key only
insert into recommendations_tbl(recommendation_userid) values(1);

-- other columns were assigned default values
select * from recommendations_tbl;

recommendation_userid | recommendation_category | recommendation_manufacturer
--------------------: | :---------------------- | :--------------------------
                    1 | diverse                 | diverse