mySQL trigger that notes when and what data is being accessed from a table in a special access table

Question

I have two table in my MySQL database access & user. I would like to create a trigger that activates every time the user table is accessed and that notes both a time stamp and all columns that are being requested.

So for example if someone requested name from user where ID=20 then the trigger would create a new row in the accessible table that noted the userID, timestamp in unix format, rows column so userID=20, timestamp=1515147950, rowAccessed=name.

How would such a trigger roughly look?

Edit:

User table (InnoDB):

| ID |  name  |    email    | age |
+----+--------+-------------+-----+
| 1  | Alice  | alice@a.com | 20  |
| 2  | Bo b   | bob@b.com   | 12  |
| 3  | Carl   | carl@c.com  | 32  |

Access table (InnoDB):

| ID | userID |  timeStamp  | column |
+----+--------+-------------+--------+
| 1  |   2    | 1515149281  | name   |
| 2  |   1    | 1515148251  | email  |

The data in the access table is what I would like the trigger to fill in.

The userID column in the Access table is linked to the ID of the user table through a InnoDB relation

Answer 1

No need to say that the best option for your question is to handle it from code. But if it is necessary to do it from Mysql... this is a approach, probably won't work, I don't have access to a MySQL to test it, but this is where I would start from:

create table user (
  ID int primary key,
  name text,
  email text,
  age int
)


create table access (
  ID int primary key auto_increment,
  userID int,
  time timestamp,
  columnName text
)


insert into user values (1, 'Alice', 'alice@alice.com', 20), (2, 'Bob', 'bob@bob.com', 25)


create procedure selectUser(colName Boolean, colEmail Boolean, colAge Boolean, id INTEGER)
BEGIN
  DECLARE justNow timestamp;
  select now() into justNow;
  IF colName THEN
    insert into access(userID, time, columnName) values (id, justNow, 'name');
  END IF;
  IF colEmail THEN
    insert into access(userID, time, columnName) values (id, justNow, 'email');
  END IF;
  IF colAge THEN
    insert into access(userID, time, columnName) values (id, justNow, 'age');
  END IF;

  SET @s = CONCAT('SELECT name FROM user');
  PREPARE stmt FROM @s;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

END

call selectUser(true, true, true, 1)

I haven't finished the part of the column query, but that's easy. Let us know if this approach works for you.