Improve company productivity with a Business Account.Sign Up

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

How to create audit fields on a table in Postgresql

I need to create a Postgresql table that contains 4 'audit' fields:

  • User ID Created
  • User Id Modified
  • Date Created
  • Date Modified

These fields should be populated through the use of defaults and / or triggers.  For example, it is fine to use default values for all 4 fields when a record is created, but after creation, the "user id created" and "date created" fields should never change.  On the other hand, I want the "user id modified" and "date modified" to change every time that the record gets updated.

I am looking for someone to help me complete the following "create table" statement:

create table test_table (id serial
                       , name character varying
                       , user_id_created
                       , user_id_modified 
                       , datetime_created 
                       , datetime_modified
                       )

Open in new window

0
jbaird123
Asked:
jbaird123
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
to deny updates on the created fields, you could deny them, but that is not part of the
DENY UPDATE ON test_table ( user_id_created) TO <users or group as you want>

to fill them on creation:
user name is a system function: current_user or user:
https://www.postgresql.org/docs/9.1/static/functions-info.html
and date/time functions:
https://www.postgresql.org/docs/9.1/static/functions-datetime.html

to specify the "default" in the syntax:
https://www.postgresql.org/docs/9.2/static/ddl-default.html

to update the "update" fields, use a trigger on the table:
https://www.postgresql.org/docs/9.4/static/sql-createtrigger.html
0
 
jbaird123Author Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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