[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

Create table with Timestamp and Modified by

Is there a way in MySQL, using only SQL to create a table that tracks
not only when a record was last modified,
but by *who*?

thanks,
-JW
0
JustinW
Asked:
JustinW
  • 2
  • 2
1 Solution
 
DataCruncherCommented:
Which one of the following scenario applies to you:

1- You have a MySQL database that is accessed directly by many users that have their own account in MySQL, in that one case you want to track the MySQL user name.  One solution for that is using MySQL triggers that would be launched everytime an update or insert operation is made on a specific table:
http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

2- You have an application that uses MySQL as the back-end database, in which case you are surely using a single MySQL user account to make all the operations and within that database you have a User table which contains the different user names and passwords used to access that application.  In that one case you have to program it in your application.
0
 
JustinWAuthor Commented:
#1)
0
 
DataCruncherCommented:
/* Here is a example on how to use triggers, we create an TEST table and a TESTA table which will be the audit table for the first one */
CREATE TABLE test(
ID int not null,
DESCRIPTION text,
PRIMARY KEY(ID)
);

/* creating the audit table */
CREATE TABLE testa(
OPERATION_TYPE VARCHAR(30) NOT NULL,
UPDATE_DATE DATETIME NOT NULL,
UPDATE_USER VARCHAR(30) NOT NULL
);

/* Creating the trigger that will be launched everytime there is an UPDATE operation on the TEST table */
CREATE TRIGGER test_after_update AFTER UPDATE ON test FOR EACH ROW
INSERT INTO testa(operation_type, update_date, update_user) values('UPDATE', now(), current_user());

/* Creating the trigger that will be launched everytime there is an INSERT operation on the TEST table */
CREATE TRIGGER test_after_insert AFTER INSERT ON test FOR EACH ROW
INSERT INTO testa(operation_type, update_date, update_user) values('INSERT', now(), current_user());

/* Some commands to test */
/* As user A (can be root) */
INSERT INTO TEST(ID, DESCRIPTION) VALUES(1, 'Test');
INSERT INTO TEST(ID, DESCRIPTION) VALUES(2, 'Another Test');
UPDATE TEST SET DESCRIPTION = 'New Test' WHERE ID = 2;
/* Now swicth to another MySQL user */
INSERT INTO TEST(ID, DESCRIPTION) VALUES(3, '3rd test');
UPDATE TEST SET DESCRIPTION = 'Last Test' WHERE ID = 3;

/* Now check the audit table and you should see the 5 records inserted for all the INSERT and UPDATE operation */
SELECT * FROM TESTA;
0
 
JustinWAuthor Commented:
perfect!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now