1

Let's say the database have 5 tables for sake of simplicity (there're many more in reality). Table 1 has the basic information of a user which is not changed so often. However, the other tables are accessed very often. All these other tables have the user-id as the foreign key. I need to make a column lastAccess in table 1 such that whenever the user works with any of the table, user's lastAccess in table 1 is updated.

I'm working with [python-socketio] (https://github.com/miguelgrinberg/python-socketio) server connected to PostgreSQL database using python-psycopg2 connector.

It seems like a task which every person working with databases should know. I am new to working with RDBMS and exactly find a way to do it. A general direction, if a solution is not possible, will also be really appreciated.

  • how are you defining "user works with any of the table", SELECT or UPDATE? – Evan Carroll Dec 23 '16 at 19:22
  • The user does either UPDATE or INSERT. – Tushar Jain Dec 23 '16 at 19:35
  • Would you be ok with putting the UPDATE/INSERT time on each table, or do you really want one time for all five tables? – Evan Carroll Dec 23 '16 at 19:37
  • Actually, the database has much more than just 5 tables (for the sake of simplicity, let's assume 5). I think the better option would be have one time for all, right ? – Tushar Jain Dec 23 '16 at 19:43
  • 1
    Yaa, space is not an issue. – Tushar Jain Dec 23 '16 at 20:15
0

First, I would advise against access as a term. I suggest mtime myself, but "access" is horrible. Access implies SELECT to me.

Rather than putting one access time on a user table, and having every trigger point to it consider putting one modified-time (mtime) on every row, in all tables. If you don't have an modified-time (mtime), you can create it.

ALTER TABLE tbl ADD COLUMN mtime timestamp DEFAULT CURRENT_TIMESTAMP;

If the tables aren't created yet, I would make the above NOT NULL also. After the column is created, follow the instructions on this answer

If you have one timestamp on table foo, being referenced by multiple triggers, then you get into an area where you can either

  • have a race condition or a locking condition.
  • require a more complex write-operation.

I wouldn't do the above. And it sounds less useful. If you want a time on a user table, I would make it relevant to the user by perhaps updating it when the user logs in or something of that sort. I would not store metadata from other tables on my user table.

1

I have two solutions:

  1. Create a lastAccess column on all 5 of the tables. Create database triggers on each table to update it's corresponding lastAccess column. This will allow you to track when each table is updated.

  2. Create lastAccess on Table 1. Then create triggers on tables 2,3,4,5 that will update the lastAccess column on Table 1.

  • Could you please show how to create such a trigger? Can we create a single trigger for multiple tables (2nd option) ? – Tushar Jain Dec 23 '16 at 19:40
  • @TusharJain you'll need a single triggger function and one update/insert trigger in each table - to call this function. See my answer here (in a different problem) for an example: dba.stackexchange.com/questions/157501/… – ypercubeᵀᴹ Dec 24 '16 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.