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?

Answer 1

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.

Assuming that you properly defined default values for each column excepted recommendation_userid, all you have to do is not pass these additional columns when inserting. MySQL will automatically assign the default value to the columns that you did not provide when inserting.

So your INSERT command should just look like:

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

This will continue to work even if new columns are created in the target table (again, assuming that these new columns have a properly defined default value).

Demo on 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