CODEWITHSUNDEEP
mysqltriggers
user2320499
user2320499

Reputation: 73

can we have multiple triggers on a single table in mysql

i have written a before insert trigger and after insert trigger on a single table in MySQL. But the one of the trigger automatically gets replaced when i put the other trigger.if i put after insert trigger , the before insert trigger code automatically gets replaced. Note it gets deleted or you can the the code gets deleted.

Both of them separately work fine. Please help me on this.

Upvotes: 7

Views: 16198

Answers (3)

Ed Gibbs
Ed Gibbs

Reputation: 26363

Note: The first half of this answer applies to MySQL prior to v5.7.2. See the answer below from @steffen for v5.7.2 and higher. The second half is still valid: the OP was using the same name when trying to implement multiple triggers.

You can have a BEFORE INSERT trigger and an AFTER INSERT trigger. It's in the documentation. You can only have one of each - for example, you can't have two (or three or four) BEFORE INSERT triggers.

If your AFTER INSERT trigger is wiping out your BEFORE INSERT trigger then the two triggers probably have the same name. Make sure each has a unique name.

Upvotes: 7

Zhang
Zhang

Reputation: 11

From the MySQL Version 8.0 Documentation

It is possible to define multiple triggers for a given table that have the same trigger event and action time. For example, you can have two BEFORE UPDATE triggers for a table. By default, triggers that have the same trigger event and action time activate in the order they were created. To affect trigger order, specify a trigger_order clause that indicates FOLLOWS or PRECEDES and the name of an existing trigger that also has the same trigger event and action time. With FOLLOWS, the new trigger activates after the existing trigger. With PRECEDES, the new trigger activates before the existing trigger.

Upvotes: 1

steffen
steffen

Reputation: 17058

Before MySQL 5.7.2

You can have only one trigger per table and trigger event and action time. For example it's possible to have

  • one BEFORE UPDATE and one AFTER UPDATE or
  • one BEFORE UPDATE and one BEFORE INSERT

trigger on a table. But you can have triggers that execute multiple statements by using BEGIN ... END though. See MySQL 5.6 Documentation for more information.

With MySQL 5.7.2

There are no restrictions about the number of triggers per table any more. It's moreover possible to define the order of trigger processing with the PRECEDES and FOLLOWS keywords. See MySQL 5.7 Documentation for more information.

Upvotes: 18

Related Questions