[MDEV-6112] multiple triggers per table Created: 2014-04-15  Updated: 2018-08-31  Resolved: 2016-10-04

Status: Closed
Project: MariaDB Server
Component/s: Triggers
Fix Version/s: 10.2.3

Type: Task Priority: Major
Reporter: Sergei Golubchik Assignee: Michael Widenius
Resolution: Fixed Votes: 2
Labels: Compatibility

Issue Links:
PartOf
is part of MDEV-10137 Providing compatibility to other data... Open
Relates
relates to MDEV-10915 Multiple triggers are not counted in ... Closed
relates to MDEV-10916 In trigger's CREATED time microsecond... Closed
relates to MDEV-10924 CREATE OR REPLACE TRIGGER which fails... Closed
relates to MDEV-10926 Extra dot (period) in the error text ... Closed
relates to MDEV-10930 FOLLOWS in lex.h is out of alphabetic... Closed
relates to MDEV-10911 Make SHOW TRIGGERS to show triggers' ... Closed
relates to MDEV-11033 "follows" does not show up in "show c... Closed
Epic Link: Oracle Compatibility
Sprint: 10.2.2-3, 10.2.2-2, 10.2.2-4

 Description   

Many triggers for the same event per table (e.g. many BEFORE DELETE triggerts).
This can be ported from 5.7

Looked at the MySQL 5.7 implementation. Too complex and too many not needed changed and moving of things to different files that make it very hard to follow code changes over time.

Will do this with new code, but will take test cases from MySQL 5.7

New functionality:

  • "Any" amount of same events
  • New syntax with { FOLLOWS | PRECEDES } trigger_name

    CREATE [OR REPLACE]
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER [IF NOT EXISTS] trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [{ FOLLOWS | PRECEDES }

    other_trigger_name ]
    trigger_stmt

 Comments   
Comment by Federico Razzoli [ 2016-09-20 ]

Can this be disabled? Multiple triggers of the same type can be useful, but can also be chaotic and hard to debug...

Comment by Michael Widenius [ 2016-09-27 ]

ready for review and testing

Comment by Michael Widenius [ 2016-09-27 ]

As it's completely optional to add a trigger for table, I don't see any reason to disable this.
We already have way too many startup options for MariaDB.
If one doesn't think a person is capable of using TRIGGER's, then one shouldn't give him the TRIGGER privilege.

Comment by Federico Razzoli [ 2016-09-27 ]

Almost every feature can be limited in some way, the strangest example is sql_safe_updates.
But I realized that my proposal would add an incompatibility with MySQL, so it isn't a good idea.

Comment by Michael Widenius [ 2016-09-28 ]

Another reason for not adding an option for this is that multiple triggers per table is standard ANSI SQL feature.

Optionally disabling all features in SQL that may be problematic for some users is task independent of this one. For example, I think a lot of people would find it useful if DROP TABLE was disabled after they have dropped an important table

Comment by Michael Widenius [ 2016-10-04 ]

Feature pushed into 10.2

Generated at Thu Feb 08 07:09:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.