CODEWITHSUNDEEP
mysqltriggers
makingitwork
makingitwork

Reputation: 149

is it possible to have multiple after insert trigger in one table?

I have multiple users that I want to be able to trigger my triggers, since you cannot have multiple definer in a single trigger, I decided to have multiple trigger instead for each user.

so in one of my table I already have a trigger called system_after_insert which is assigned to root, now I want to have another trigger assign to admin which will be called system_after_insert_1, now both of them have the same content and are found in the same table. I already have system_after_insert trigger created, but now when I tried to add system_after_insert_1 it returns an error saying:

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

CREATE DEFINER=`admin`@`localhost` TRIGGER system_after_insert_1 
    AFTER INSERT ON `system_database`.atm_account
    FOR EACH ROW 
BEGIN
    INSERT INTO `log_database`.system_logs
    SET actionDone = 'insert',
        employee = USER(),
        logDate = NOW(),
        tableChange = 'atm_account',
        rowChange = NEW.atmID,
        new_data = concat("Account #",NEW.accountNumber,", Pin:",NEW.pin,", Security Code:",NEW.securityCode,", Withdrawal Limit:",NEW.withdrawalLimit);  

END

not that the above code is the same with system_after_insert the only difference is the trigger name and the definer.

Upvotes: 0

Views: 839

Answers (1)

Zafar Malik
Zafar Malik

Reputation: 6844

You can create maximum 6 triggers on a table with below combinitions till mysql 5.6 version:

After/Before

insert/update/delete

Means:

  1. After Insert
  2. Before insert
  3. after update
  4. before update
  5. after delete
  6. before delete

But below mysql 5.7.2 documentation is showing that it can be possible, so you can check here if using Mysql5.7.2.

http://dev.mysql.com/doc/refman/5.7/en/create-trigger.html

Upvotes: 1

Related Questions