As P.Salmon suggested in his comment, here is the short description of what I would like to achieve:
I have a Testing2 tatble with just two columns (check example below). I regularly do a simple insert which can look like this:
INSERT INTO Testing2 (gasessiondurationbucket, gasessions)
VALUES
(51815,20),
(3654,18),
(25981,28);
I also have a second table into which I would like to insert a single row log about the whole insert to Testing2, this table is called Insert_Log_For_Testing_Tables and has 4 columns (insert_id, created_date, created_by, ID_of_Insert). The ID_of_Insert is generally not important, I used this column to test some of my solutions (you can read about this below).
What I would like to achieve is to create a trigger that would fire after each insert into Testing2 table and create a single log in my log (Insert_Log_For_Testing_Tables) table. So for example after insert using the query above, i would like this trigger to log this:
| insert_id | created_date | created_by | ID_of_Insert |
| --------- | --------------------- | ------------------------- | ------------ |
|'1' |'2020-03-14 16:54:12' | 'testing@IP' | |
| --------- | --------------------- | ------------------------- | ------------ |
This is how the Testing2 table would look like after this insert:
| gasessiondurationbucket | gasessions |
| ----------------------- | ---------- |
| '51815' |'20' |
| '3654' |'18' |
| '25981' |'28' |
| ----------------------- | ---------- |
After another insert using a simple insert query i would like to create another log in this table, so after two inserts the Insert_Log_For_Testing_Tables table would look like this:
| insert_id | created_date | created_by | ID_of_Insert |
| --------- | --------------------- | ------------------------- | ------------ |
|'1' |'2020-03-14 16:54:12' | 'testing@IP' | |
|'2' |'2020-03-14 16:56:15' | 'testing@IP' | |
| --------- | --------------------- | ------------------------- | ------------ |
And the Testing2 table would for example look like this:
| gasessiondurationbucket | gasessions |
| ----------------------- | ---------- |
| '51815' |'20' |
| '3654' |'18' |
| '25981' |'28' |
| '96541968' |'50' |
| '496185' |'48' |
| '3168' |'36' |
| '81656' |'55' |
| ----------------------- | ---------- |
Is there a way to achieve this using only MySQL version 5.7?
Below are described some approaches i tried, one worked, but i do not think it is the ideal solution, other unfortunately did not do what i need.
"----------------------------------------------------------------------------------------------------------------------------------"
I am trying to create a trigger that would fire after each insert into a table and create a log in a different table with data about this insert.
This is some example data from my table called Testing2 into which I regularly insert data.
| gasessiondurationbucket | gasessions |
| ----------------------- | ---------- |
| '2035', |'1' |
| '1783', |'1' |
| '1777', |'1' |
| '1187', |'1' |
| '1103', |'1' |
| '811', |'1' |
| '644', |'1' |
| '568', |'1' |
| '546', |'1' |
| '435', |'1' |
| '320', |'1' |
| '135', |'1' |
| '130', |'1' |
| '65', |'1' |
| '42', |'1' |
| '36', |'1' |
| '34', |'1' |
| '21', |'1' |
| '15', |'1' |
| '0', |'19' |
| ----------------------- | ---------- |
This is the information about this table:
'CREATE TABLE `Testing2` (`gasessiondurationbucket` int(11) DEFAULT NULL, `gasessions` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8'
Now the second table is called Insert_Log_For_Testing_Tables and it looks like this:
| insert_id | created_date | created_by | ID_of_Insert |
| --------- | --------------------- | ------------------------- | ------------ |
|'1', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'2', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'3', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'4', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'5', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'6', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'7', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'8', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'9', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'10', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'11', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'12', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'13', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'14', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'15', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'16', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'17', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'18', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'19', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'20', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
| --------- | --------------------- | ------------------------- | ------------ |
This is the information about this second table:
'CREATE TABLE `Insert_Log_For_Testing_Tables` (
`insert_id` int(11) NOT NULL AUTO_INCREMENT,
`created_date` datetime DEFAULT NULL,
`created_by` varchar(25) DEFAULT NULL,
`ID_of_Insert` int(11) DEFAULT NULL,
PRIMARY KEY (`insert_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8'
Basically every insert into the first table consists of random amount of the two values, meaning sometimes I insert 20 new values, sometimes I insert 100 values.
What I would like to achieve is to make a single log using a trigger every time I do a new insert.
The problem I have is the FOR EACH ROW part of the trigger.
When I write the trigger simply like this:
CREATE DEFINER=`root`@`%` TRIGGER `MyDatabase`.`Testing2_AFTER_INSERT` AFTER INSERT ON `Testing2` FOR EACH ROW
BEGIN
DECLARE vUser varchar(50);
SELECT USER() INTO vUser;
INSERT INTO Insert_Log_For_Testing_Tables
( created_date,
created_by)
VALUES
( SYSDATE(),
vUser);
END
It works and fills insert_id(auto increment), created_date and created_by, but by its definition it does this for every new inserted row, meaning I get the log multiple (check Insert_Log_For_Testing_Tables Table) times whereas I would like just a single row log. What I came up with is that I can do delete after every insert to log table based on created_date, created_by and ID_of_Insert so it looks like this:
CREATE DEFINER=`root`@`%` TRIGGER `MyDatabase`.`Testing2_AFTER_INSERT` AFTER INSERT ON `Testing2` FOR EACH ROW
BEGIN
DECLARE vUser varchar(50);
SELECT USER() INTO vUser;
INSERT INTO Insert_Log_For_Testing_Tables
( created_date,
created_by)
VALUES
( SYSDATE(),
vUser);
DELETE t1 FROM Insert_Log_For_Testing_Tables t1
INNER JOIN Insert_Log_For_Testing_Tables t2
WHERE
t1.insert_id < t2.insert_id AND
t1.created_date = t2.created_date AND
t1.created_by = t2.created_by AND
t1.ID_of_Insert = t2.ID_of_Insert;
END
This trigger works for me and only leaves one record in my log table after each and every insert with the biggest insert_id number, so when I create insert with another 20 rows I get the following log:
| insert_id | created_date | created_by | ID_of_Insert |
| --------- | --------------------- | ------------------------- | ------------ |
|'20', |'2020-03-14 16:54:12', | 'testing@IP', | '2' |
|'40', |'2020-03-14 16:56:15', | 'testing@IP', | '2' |
| --------- | --------------------- | ------------------------- | ------------ |
What I wonder though is that, because I am not sure how this works under the hood, for example, if I were to insert a lot of rows into the Testing2 table, lets say over million, if created_date into which I insert just SYSDATE() for each row (beacause of the FOR EACH ROW part of the trigger) could differ for some rows, meaning when i would do the delete part I might actually end up with two logs for one insert because the created_date column would contain more unique values (first 500000 rows would be inserted at 2020-03-14 16:54:12 and the other 500000 rows would be inserted at 2020-03-14 16:54:13) . I am not sure if this is possible, but I do not want to take any chances, so i thought i could try something else.
My second approach to my problem was to actually create two triggers (one BEFORE INSERT and one AFTER INSERT) and one stored procedure.
Now the BEFORE INSERT stored procedure is really simple and looks like this:
CREATE DEFINER=`root`@`%` TRIGGER `MyDatabase`.`Testing2_BEFORE_INSERT` BEFORE INSERT ON `Testing2` FOR EACH ROW
BEGIN
call MyDatabase.new_procedure('1', @IDOfRow2);
END
It just calls stored procedure with IN parameter of 1, I do this only to set up my one variable.
My stored procedure is also very simple:
CREATE DEFINER=`root`@`%` PROCEDURE `new_procedure`(IN IDOfRow1 INT, OUT IDOfRow2 INT)
BEGIN
SELECT IDOfRow1 INTO IDOfRow2;
END
I only want to assign value of 1 to the variable so I can use it inside of the AFTER INSERT trigger.
The AFTER INSERT trigger looks like this:
CREATE DEFINER=`root`@`%` TRIGGER `MyDatabase`.`Testing2_AFTER_INSERT` AFTER INSERT ON `Testing2` FOR EACH ROW
BEGIN
DECLARE vUser varchar(50);
SELECT USER() INTO vUser;
SET @IDOfRow2 = @IDOfRow2 + 1;
IF @IDOfRow2 = 2 THEN
INSERT INTO Insert_Log_For_Testing_Tables
( created_date,
created_by,
ID_of_Insert)
VALUES
( SYSDATE(),
vUser,
@IDOfRow2);
END IF;
END
What I thought is that because od this line: SET @IDOfRow2 = @IDOfRow2 + 1; I would be able to change the value of @IDOfRow2 to 2 and therefore insert into the Insert_Log_For_Testing_Tables table only the first record - log, nothing else would be inserted because @IDOfRow2 would be bigger than 2. But unfortunately this does not work as, according to the research I've done, the BEFORE INSERT triggers for the first row, then AFTER INSERT triggers for the first row, then BEFORE INSERT triggers for the second row, then AFTER INSERT triggers for the second row etc. This happens for each and every row, so both triggers kind of alternate during the execution, meaning that my variable always gets refreshed so I insert log for every row, instead of just one for every insert.
Is there some other solution I could try to achieve what I need, or should I just stick with the DELETE statement inside of my AFTER UPDATE trigger and not worry about duplicated logs?
I would feel better if there is some solution similar to the one with two triggers and variable one which I proposed. Is there any solution to my problem or do I have to stick with the DELETE statement one?
Thank you very much, if there is something unclear please comment and I will try to clarify.
