3

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();
3
  • Add the SET NEW.createdAt = NOW(); to the existing triggers.
    – Rick James
    Jun 30, 2015 at 23:56
  • 2
    Starting with MariaDB 10.2.3 you can have multiple triggers for the same trigger_time and trigger_event. See mariadb.com/kb/en/mariadb/create-trigger
    – cgaldiolo
    Mar 1, 2017 at 18:51
  • Just to add to @cgaldiolo MySQL 5.7.2 also supports multiple triggers for the same action time and event Feb 21, 2018 at 13:32

2 Answers 2

3

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';
2

It means you already have a trigger for BEFORE INSERT ON companies.
(action time = BEFORE, event = INSERT)
You can't have two of them for the same event and action time.

You can fix it by editing the existing BEFORE INSERT trigger.

5
  • 1
    Yet another absolutely shocking indictment of MySQL. Not sure about PostgreSQL, but Interbase/Firebird has had multiple triggers on all actions for at least two decades <shakes head in disbelief>.
    – Vérace
    Jun 30, 2015 at 18:06
  • @Vérace I find this the least of MySQL problems. Jun 30, 2015 at 18:13
  • Agreed - it's just yet another gripe I have. If you look for books about MySQL on Amazon, you get 4429 results, PostgreSQL 620 & Firebird/Interbase 63. That speaks for itself - two vastly superior databases trailing miserably in terms of popularity and usage.
    – Vérace
    Jun 30, 2015 at 18:22
  • @Vérace, not sure about the two decades for PostgreSQL but at least version 7.1 from 2001 had support for multiple triggers. Jun 30, 2015 at 18:29
  • I was sure of it - I just wasn't sure of the date. I was working with Interbase approx. 2 decades ago, so I was certain of my ground on that one.
    – Vérace
    Jun 30, 2015 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.