How to create audit fields on a table in Postgresql
I need to create a Postgresql table that contains 4 'audit' fields:
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:
- 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
)
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial