is this possible? adding 4 new columns: createdDate,modifiedDate,createdBy,modifiedBy?

Question

I'm on sql developer. I have a table. I Want to add those 4 new columns which I know how to do, but I want those values to not be entered by the user when he enters a new row or edits an existing row, I want those values to be automatically filled

For example if the user enters

insert into tableName values (val1,val2,val3)

then the table will have the 7 new values in the new row:

val1,val2,val3,createdDate,modifiedDate,createdBy,modifiedBy

same when the user modifies a value in an existing row

update TAbleName set val1 = newVal where id = id1

and then the "modifiedDate" and "modifiedBy" fields in that row will be automatically modified

Answer 1

What database do you use?

For auto fill when you add a new row, you need to setup "default binding" aka "default field"

ALTER TABLE YourTable 
    ADD CONSTRAINT DF_YourTable DEFAULT GETDATE() FOR YourColumn

For update, you need to make a trigger to edit the column

How to: Create trigger for auto update modified date with SQL Server 2008

Answer 2

Partially, column's default value can do that (for created date and user who did that); for modifications, use a trigger.

Here's an example:

SQL> create table test (id number, name varchar2(20));

Table created.

SQL> alter table test add
  2    (created_date    date           default sysdate,
  3     created_by      varchar2(30)   default user,
  4     modified_date   date,
  5     modified_by     varchar2(30)
  6    );

Table altered.

SQL> insert into test (id, name) values (1, 'Little');

1 row created.

SQL> select * From test;

        ID NAME                 CREATED_DATE        CREATED_BY MODIFIED_DATE       MODIFIED_B
---------- -------------------- ------------------- ---------- ------------------- ----------
         1 Little               13.02.2020 22:23:17 SCOTT

Updating a row a little bit later - nothing has changed (for created and modified columns):

SQL> update test set name = 'Foot' where id = 1;

1 row updated.

SQL> select * From test;

        ID NAME                 CREATED_DATE        CREATED_BY MODIFIED_DATE       MODIFIED_B
---------- -------------------- ------------------- ---------- ------------------- ----------
         1 Foot                 13.02.2020 22:23:17 SCOTT

Let's create a trigger. It's a simple one:

SQL> create or replace trigger trg_testmod_bu
  2    before update on test
  3    for each row
  4  begin
  5    :new.modified_date := sysdate;
  6    :new.modified_by   := user;
  7  end;
  8  /

Trigger created.

SQL> update test set name = 'Bigfoot' where id = 1;

1 row updated.

SQL> select * From test;

        ID NAME                 CREATED_DATE        CREATED_BY MODIFIED_DATE       MODIFIED_B
---------- -------------------- ------------------- ---------- ------------------- ----------
         1 Bigfoot              13.02.2020 22:23:17 SCOTT      13.02.2020 22:26:38 SCOTT

Right; the trigger updated both modified columns.