how can i modify or manipulate this trigger

Question

I'm trying to create a trigger which will capture any event that will occur when I update any column in the table before and after updating, let's say I have 4 columns:

first_name  address  city  country

Let's say I edited first_name lets say Jack to Henk. It should insert in another table the command (i.e. update) ,time , description but inside the description I want it to write Jack was changed to John by current user(i.e using the current-user () function),if it is a city being updated from Mechinkova to Tostov, it should do the same do with other columns.

I know I want to have to add the concat function inside the trigger, I want it to be like this for example:

DROP TRIGGER IF EXISTS adminpanel.soft//
CREATE TRIGGER adminpanel.soft BEFORE UPDATE ON adminpanel.aggrement
FOR EACH ROW
BEGIN
INSERT INTO adminpanel.aggretrigger(cmd, time, cmd_user, last_name, city) VALUES("INSERT", NOW(), CURRENT_USER(), new.last_name, new.city);
END
//

Answer 1

What you are asking for is an audit trigger. It is very easy to implement.

Let us first slightly modify your main table. Let's add a field id of integer datatype as the primary key to the table, so your table would look like:

tablename
( id            integer PK
, first_name    varchar
, address       varchar
, city          varchar
, country       varchar
)

Now, you will need a table, say UNIVERSAL_AUDIT_ENTRY table that will store the changes made to the data in your schema.

From what experience I have, I suggest you create this table as follows:

universal_audit_entry
( universal_audit_entryid integer PK
, table_name              varchar -- captures the name of the table
, column_name             varchar -- captures the name of the column
, entry_type              varchar -- captures the event, e.g., 'INSERT' or 'UPDATE'
, primary_key_value       integer -- captures, e.g., the value in tblename.id
, from_str                varchar -- captures the value that was present before
, to_str                  varchar -- captures the value that was changed into
, timestamp               datetime -- captures the timestamp of the event
, username                varchar -- captures the name of user
)

Now with the universal_audit_entry table ready, your trigger should look somewhat like:

CREATE TRIGGER adminpanel.soft
BEFORE UPDATE ON adminpanel.aggrement
FOR EACH ROW
BEGIN
  IF UPDATING(first_name) THEN
    INSERT INTO universal_audit_entry VALUES
    ( 123 -- example for universal_audit_entryid
    , 'TABLENAME'
    , 'FIRST_NAME'
    , 'UPDATE'
    , new.id
    , old.first_name
    , new.first_name
    , current_timestamp()
    , current_user);
 END IF;
END;
//

You can use similar logic to audit more columns in the same table and other tables also.

Note:

1. This code is not tested. I have added it here only for illustration purposes. This code for trigger is not supposed to be used directly.

2. new and old are the pseudo-records that are generated during an update statement. These records correspond to the rows that are being updated. :new means the row after the update statement runs and :old means the row before the update statement runs. This works in Oracle. Kindly make sure if it works in MySQL also.

---

EDIT

You can read more about MySQL triggers here. Read more about audit trail here and this SO question.