MariaDB – Error with Multiple Triggers for One Table

mariadbMySQLtrigger

I'm trying to run this query in mysql Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1 but got this error all the time:

CREATE TRIGGER trig_companies_table_insert BEFORE INSERT ON `companies`
    FOR EACH ROW SET NEW.createdAt = NOW();
[Err] 1235 - This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table'

What this means? How I can avoid and/or fix it?

This is the queries I am trying to execute:

-- brands
ALTER TABLE `brands`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_brands_table_insert BEFORE INSERT ON `brands`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- companies
ALTER TABLE `companies`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_companies_table_insert BEFORE INSERT ON `companies`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- emails
ALTER TABLE `emails`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_emails_table_insert BEFORE INSERT ON `emails`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- events_tracking
ALTER TABLE `events_tracking`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_events_tracking_table_insert BEFORE INSERT ON `events_tracking`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- media
ALTER TABLE `media`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_media_table_insert BEFORE INSERT ON `media`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- messages
ALTER TABLE `messages`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_messages_table_insert BEFORE INSERT ON `messages`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- reps
ALTER TABLE `reps`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_reps_table_insert BEFORE INSERT ON `reps`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- targets
ALTER TABLE `targets`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_targets_table_insert BEFORE INSERT ON `targets`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- territories
ALTER TABLE `territories`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_territories_table_insert BEFORE INSERT ON `territories`
    FOR EACH ROW SET NEW.createdAt = NOW();

Best Answer

This error means you already have an BEFORE INSERT trigger on companies table.

If it is the same trigger (meaning trig_companies_table_insert) that you created earlier and now you want to replace it then you need to drop it first:

DROP TRIGGER trig_companies_table_insert;
DELIMITER $$  
CREATE TRIGGER trig_companies_table_insert BEFORE INSERT ON `companies`
FOR EACH ROW SET NEW.createdAt = NOW();
END$$
DELIMITER ;

Now if you have some other trigger you have to merge code from both triggers into one, then drop existing trigger, and then create a new one.

To show the list of existing triggers use SHOW TRIGGERS.

SHOW TRIGGERS WHERE `table` = 'companies';
Related Question