DML triggers are either defined as table level or as row level. A table level trigger fires once for each operation on the table, so if you update 30 rows then that is one operation as far as a table trigger is concerned. Table triggers do not have insight into which rows are modified, but … Read more
From the fine manual: 36.1. Overview of Trigger Behavior […] For a row-level trigger, the input data also includes the NEW row for INSERT and UPDATE triggers, and/or the OLD row for UPDATE and DELETE triggers. Statement-level triggers do not currently have any way to examine the individual row(s) modified by the statement. And from … Read more
For sorting reasons I would recommend a structured approach: TR_Users_AD TR_Users_BD TR_OtherTable_AI and so on. In the end it’s up to you – but whatever you choose to do, stay consistent, at least within the same database.
I just want to call out couple of points: Use code generators You can’t have a single procedure to track all tables, you will need to generate similar but distinct triggers on each tracked table. This kind of job is best suited for automated code generation. In your place I would use an XSLT transformation … Read more
onEdit is invoked by a Simple Trigger when a user changes a value in a spreadsheet. However, simple triggers cannot access services that require authorization, such as UrlFetchApp.fetch. See the Google Apps Script guide What you can do is to simply rename the function onEdit to something else, such as atEdit, so as to remove … Read more
drop table if exists comments; create table comments ( comment_id int unsigned not null auto_increment primary key, user_id int unsigned not null ) engine=innodb; drop table if exists activities; create table activities ( activity_id int unsigned not null auto_increment primary key, comment_id int unsigned not null, user_id int unsigned not null ) engine=innodb; delimiter # … Read more
OLD and NEW are null or not defined in a statement-level trigger. Per documentation: NEW Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations. OLD Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in … Read more
1436 – Thread stack overrun: 6136 bytes used of a 131072 byte stack, and 128000 bytes needed. The error 1436 corresponds to ER_STACK_OVERRUN_NEED_MORE in the mysql 5.1 code : malff@linux-8edv:include> pwd /home/malff/BZR_TREE/mysql-5.1/include malff@linux-8edv:include> grep 1436 mysqld_error.h #define ER_STACK_OVERRUN_NEED_MORE 1436 The code printing the error seen is in sql/sql_parse.cc, function check_stack_overrun() : bool check_stack_overrun(THD *thd, long … Read more
Not sure if it is pertinent to the OP’s question anymore, but in case you came here to find out how to prevent recursion or mutual recursion from happening in a trigger, you can test for this like so: IF TRIGGER_NESTLEVEL() <= 1/*this update is not coming from some other trigger*/ MSDN link
The insert syntax is INSERT INTO table (columns_list) VALUES (values_list) so your insert would look something like this (i’m not a MySQL expert but you can fit the query): INSERT INTO data_tracking (`data_id` , `field` , `old_value` , `new_value` , `modified` ) VALUES (NEW.data_id, ‘field1’, OLD.field, NEW.field, CURRENT_DATETIME()); And repeat it for variation on field2 … Read more