The main question in this task is the order of trigger activation. Apart from being convenient for the users, that order should be preserved during replication, dump/restore and backup scenarios. Quotes from The SQL Standard (2011 draft) ----------------------------------------- - File: 7IWD1-02-Foundation-2011-07.pdf (2011 draft) Section: 4.39 Triggers Page: 145 The order of execution of a set of triggers is ascending by value of their timestamp of creation in their descriptors, such that the oldest trigger executes first. If one or more triggers have the same timestamp value, then their relative order of execution is implementation-defined. - File: 7IWD1-02-Foundation-2011-07.pdf (2011 draft) Section: 11.49 Page: 743 - File: 7IWD1-11-Schemata-2011-07.pdf - Section: 5.3 CARDINAL_NUMBER domain Page:11 CREATE DOMAIN CARDINAL_NUMBER AS INTEGER CONSTRAINT CARDINAL_NUMBER_DOMAIN_CHECK CHECK ( VALUE >= 0 ); - Section: 5.6 TIME_STAMP domain Page: 14 CREATE DOMAIN TIME_STAMP AS TIMESTAMP(2) WITH TIME ZONE; - Section: 6.62 TRIGGERS base table Page: 245 CREATE TABLE TRIGGERS (. ACTION_ORDER INFORMATION_SCHEMA.CARDINAL_NUMBER CONSTRAINT TRIGGERS_ACTION_ORDER_NOT_NULL NOT NULL. CREATED INFORMATION_SCHEMA.TIME_STAMP. Mysqldump dumps triggers along with tables, as these are part of the table definition. When you back-up on the same device as the database, this produces. (Actually, --skip-extended-insert --skip-quick is sufficient because --opt is on. It can be disabled with --skip-quote-names, but this option should be given after. May 23, 2014 - There is a good question someone had relating to this here: MySQL - Trigger for updating same table after insert and a good answer that you. MySQL Database Triggers: Inserting or updating the same table. I noticed that you cannot make an update or insert in a trigger that is triggered off by an insert on the same table. You can't select from the same table in the trigger else it will mutate, but (in Oracle at least anyway), one would just use a before insert trigger. Nov 14, 2012 - INSERT INTO; UPDATE; DELETE FROM; TRUNCATE (this one can be. For now, I will focus on basic triggers on tables, as view triggers are a bit different. As you can see all users which mention MySQL got silently skipped. (trigger happy?) and I thought of that classic Strangelove quote, and just on. - Section: 6.62 TRIGGERS base table Page: 246 9) The value of ACTION_ORDER is the ordinal position of the trigger in the list of triggers with the same EVENT_OBJECT_CATALOG, EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE, EVENT_MANIPULATION, CONDITION_TIMING, and ACTION_ORIENTATION Proposal -------- * Introduce a new trigger attribute -- CREATED: - CREATED is a read-only attribute, storing the current time at trigger creation (timestamp of trigger creation); NOTE: the current time can be overridden by 'SET TIMESTAMP'. - CREATED is a persistent attribute, stored in the data dictionary (currently in TRG-files); - CREATED is a pure informational attribute. Strictly speaking, it is not required in order to complete this WL task. It's proposed to introduce CREATED only because: - it is required by The Standard; - we already have INFORMATION_SCHEMA.TRIGGERS.CREATED field, which is always NULL. - The value of the CREATED attribute is set at the trigger creation time and can not be changed afterwards (the only way to change it is to drop and re-create the trigger); - CREATED is shown in the INFORMATION_SCHEMA.TRIGGERS.CREATED (INFORMATION_SCHEMA.TRIGGERS.CREATED exists currently, but is always NULL); - A new column (created) is added to the SHOW CREATE TRIGGER result-set to show CREATED; - Internally CREATED is represented by TIMESTAMP(2) (according to The SQL Standard) - According to The Standard CREATED should include timezone information. However, MySQL TIMESTAMP/DATETIME don't support timezone information. Thus, it's suggested to store timestamps in the UTC timezone. However, it's proposed that INFORMATION_SCHEMA.TRIGGERS (SHOW TRIGGERS) and SHOW CREATE TRIGGER output CREATED in the session timezone. This is in line with the current behaviour of CREATED column in INFORMATION_SCHEMA.ROUTINES, INFORMATION_SCHEMA.EVENTS and INFORMATION_SCHEMA.TABLES. - It is possible to create triggers with the same CREATED values. * Introduce a new trigger attribute -- ACTION_ORDER -- the order of trigger activation: - ACTION_ORDER is a persistent attribute. Windows hotfix downloader windows 7. The point is that the order of triggers must remain the same between user sessions / server restarts. However, for the current data dictionary (attribute lists in TRG-files) explicit storing of ACTION_ORDER is not required -- the order is well-defined (preserved) by the order of values in the TRG-attribute-lists. - ACTION_ORDER is a positive integer (greater than 0). The Standard prescribes that the ACTION_ORDER is a non-negative integer. It's proposed to not use zero value in order to distinguish between older versions, which don't support multiple triggers. - The value of the ACTION_ORDER is calculated automatically (increased) by the server at the trigger creation time; - User has no way to specify/change the exact value of the ACTION_ORDER (ACTION_ORDER is a read-only attribute); - Triggers are activated in the ascending order of ACTION_ORDER values. This statement fully complies with The Standard: - The Standard requires trigger activation in the order of created timestamp. This is satisfied as the ACTION_ORDER value is increased every time a new trigger is created. - The Standard does not specify behavior for triggers with the same created value (it's implementation-defined). This statement is semantically equal to the following: triggers are activated in the ascending order of their creation. Note, that MySQL provides a way to override current time: 'SET TIMESTAMP'. In the current proposal, overriding the current time will affect value of the CREATED attribute, but will not affect value of the ACTION_ORDER attribute. - ACTION_ORDER is shown in the INFORMATION_SCHEMA.TRIGGERS.ACTION_ORDER (INFORMATION_SCHEMA.TRIGGERS.ACTION_ORDER exists currently, but is always 0). - SHOW TRIGGERS does not show ACTION_ORDER; - SHOW CREATE TRIGGER does not show ACTION_ORDER; - ACTION_ORDER values might be implicitly changed by the server (see below); * mysqldump must dump triggers in the ascending order of ACTION_ORDER attribute. This order ensures that during the restore triggers will be re-created in the same order. * mysqldump will not preserve trigger creation timestamps (there will be no 'SET TIMESTAMP' statement before 'CREATE TRIGGER'). This is the common mysqldump behavior for other object types -- mysqldump does not prevent creation timestamps for tables, stored programs. * Additionally, the server will be extended to support the following non-standard extensions to the CREATE TRIGGER statement (Oracle 11g style): - FOLLOWS - PRECEDES For example: CREATE TRIGGER t1_ai_1a AFTER INSERT ON t1 FOR EACH ROW FOLLOWS t1_ai_1 BEGIN. END CREATE TRIGGER t1_ai_1a AFTER INSERT ON t1 FOR EACH ROW PRECEDES t1_ai_2 BEGIN. END The semantic is as follows: - FOLLOWS specifies the name of the existing trigger, after which the trigger being created should be activated; - PRECEDES specifies the name of the existing trigger, before which the trigger being created should be activated; - If FOLLOWS or PRECEDES specifies the name of non-existing trigger, an error (an SQL condition of error level) is thrown (ER_TRG_DOES_NOT_EXIST), and the CREATE TRIGGER statement fails. - FOLLOWS/PRECEDES might result in the re-numbering of ACTION_ORDER values for the existing triggers. However, the activation order remains the same. - FOLLOWS/PRECEDES will not appear in any auto-generated SQL. - FOLLOWS/PRECEDES are optional. Note that FOLLOWS/PRECEDES changes the statement 'triggers are activated in the ascending order of their creation'. Rationale --------- * Why two different attributes? An alternative approach could be to use only CREATED attribute to define activation order. However, there are the following problems with that approach: - The main problem is that creation timestamp might be the same for different triggers. It might happen for various reasons, the worst case is scripting -- triggers created by the SQL script might have (or might not, depending on the box capabilities and current load) same timestamps. - If we're going to support FOLLOWS/PRECEDES, we have to fake creation time, which is an ugly hack. Mysql Insert Trigger ExampleIn other words, there is no good way to support FOLLOWS/PRECEDES with CREATED attribute only. - Generally, in this approach, two different concepts are mixed in the CREATED attribute: - it is creation timestamp; - it is an index to specify activation order; Usually, mixing concepts complicates things. Huawei modem admin. Mysql Insert Trigger Update Same Table QuotesChanges to data dictionary -------------------------- Currently triggers are stored in TRG (TRN) files. Main definitions are stored in the TRG-file (format:.TRG). This is a plain text file, having the following structure: TYPE=TRIGGERS triggers='trigger1-definition' 'trigger2-definition' sql_modes=sqlmode1 sqlmode2 definers=definer1 definer2. Two new attributes will be added to the TRG file: - created -- the attribute will contain a list of timestamps - action_order -- the attribute will contain a list of numbers The server does not check if there are more than one trigger for every action/event in the 'triggers' attribute, meaning TRG-files can already store multiple triggers for the same action/event, and that will not crash the server. However, the order of trigger definitions does matter: 1.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |