CODEWITHSUNDEEP
mysqlmariadb
Thailand Love U
Thailand Love U

Reputation: 89

add column after insert with trigger, error 1422

I want to add new column to table in trigger but it's not working

DELIMITER //
DROP TRIGGER IF EXISTS add_dep_oncall//
CREATE TRIGGER add_dep_oncall
AFTER INSERT ON `department` 
FOR EACH ROW 
BEGIN

DECLARE col_name varchar(30);
SET col_name = NEW.department_name;

ALTER TABLE `oncall` ADD COLUMN col_name VARCHAR(255) DEFAULT NULL;

END//

I use mariadb.

After run SQL above code the error is

Explicit or implicit commit is not allowed in stored function or trigger.

Thank for any help.

Upvotes: 0

Views: 155

Answers (1)

Drew
Drew

Reputation: 24960

Stored functions and triggers do not allow transactions and commits (which would be explicit commits) nor do they allow DDL operations (which would be implicit commits).

DML - Data Manipulation Language. Operations that retrieve and set data. These should be your focus in triggers.

DDL - Data Definition Language. Operations that change the structure of tables. These should never be in a trigger.

So, your ALTER TABLE is DDL and it is disallowed.

Furthermore, DDL routines are time-costly. An ALTER TABLE could take minutes or hours to run. Even if it were allowed by the daemon, it would not survive peer review for proper use of a high performance RDBMS. Setup your schema before hand, and use it. Do not add columns on the fly except for more of a staged data migration or transformation.

There have been several questions on the Stack about adding columns on the fly in triggers. Especially from new developers to SQL that think their "on the fly" adding a column makes sense. It never does. It is usually an indicator of a poor design idea. Like, it is a new week in the year, let's add a new column.

Live triggers are no place for DDL.

Upvotes: 3

Related Questions