Hello guys,
I have a database which can be accessed by multiple users. Is there any way I can set the tables such that whenever somebody enters a new record or modifies a record, his/her username and the date entered is stored into 2 columns in the same table as the record itself. The table would look like this:
Id | Name | Username | Date Entered | Date Modified
1 | Cat | john | 1999-05-05 | 1996-06-06
I am using a GUI which is phpMyAdmin.
Thanks!
You can set a column to not allow null values, either when you create the table using NOT NULL after the data type declaration. Otherwise, you use an ALTER TABLE statements to change a column if they already exist, or you are adding the column to an existing table.
That will stop someone from adding a record, but not update. If you have a separate table of users to reference, you would use a foreign key relationship to make sure that if the user column is populated, it will be done with a valid user.
DEFAULT constraints can be used to set the value of the date fields if a value is not provided.
ALTER TABLE x
ADD USER VARCHAR(45) NOT NULL
ALTER TABLE x
ADD DATE_ENTERED DATE NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
ALTER TABLE x
ADD DATE_ENTERED DATE NOT NULL DEFAULT ON UPDATE CURRENT_TIMESTAMP
First of all, you may want to spend some time thinking about the best way to design an audit mechanism, because adding two columns to every table in your DB is probably not optimal.
Having said that, what you are looking for to do what you described is a trigger. This is a piece of SQL that will execute every time a specified operation is invoked. A rough example for your case:
create trigger audit after insert on "table_name" insert (user, time) into "table_name"
I don't remember the precise mysql syntax, but that's a rough example of what you need. Please ask a follow-up question if that isn't clear.