0

I have a column Quantity in a table Inventory of MySQL which gets updated from multiple sources. I needed to maintain a track in the table on another column called QuantityLog on the last updated time of the Quantity and the source which did it. Something like this should be the content of QuantityLog column (Text type) (only the latest update details is required):

 <Log>
     <UpdateTime>2015-02-23 12:00:01 PM</UpdateTime>
     <Source> Feeder application</Source>
 </Log>

I am aware of how to do it using trigger if only the update time is required. However, with the trigger approach is there any other mechanism to get the source and use this too?

Do note pls that I am trying to perform this via triggers only as any other mechanisms of using my application to do this will require me to change in all applications that make this change and I am not inclined to do that.

Kallol
  • 264
  • 1
  • 12
  • If you store the timestamp in the same table, you can add the column with the `ON UPDATE CURRENT_TIMESTAMP` clause, which will make it update automatically on each update of the row. You won't need a trigger, and you also won't require need any application change (unless your application uses `select *` and fails on the extra field, but that would be good to fix anyway). See [Timestamp initialization](https://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html). – GolezTrol Jul 05 '15 at 08:54

1 Answers1

1

There is no way MySql can know the "feeder application", unless there is a variable or table filled with that value. If you have this, it is easy to create a trigger that updates this info into the Inventory table on each change of the Quantity field.

However, if your applications use unique mysql users to connect to the database, you can of course use the CURRENT_USER() built in function inside your TRIGGER. Alternatively, CONNECTION_ID() might be helpful when tracking who did what. For example, you can create a new table that logs the connection id of your application. In that table you could write the application name, the PID and other stuff. Of course this would mean to change our application a bit by adding the appropriate insert statement after a connection is established. The overhead should be small, since usually connections are held in pools and do not get re-created all the time.

luksch
  • 11,497
  • 6
  • 38
  • 53