Member Avatar for kartisathis

how to find the last dml action held on which row in a table using mysql

plz do me a favour ,

tanks in advance..

  • 2 Contributors
  • 8 Replies
  • 309 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by kartisathis

Dani AI

Generated

Short answer: you can’t reliably find which row was last modified unless something recorded that change when it happened. As discovered, adding a modification-timestamp column is the simplest future-proof fix; ’s caution is also valid — you must decide whether timestamps should reflect every possible change (including manual edits) or only application-controlled updates.

Practical options and trade‑offs:

  • Application-managed timestamp: set an updated_at value from your app when it performs a legitimate update. Gives you control over what counts as a change.
  • Database-managed timestamp: automatic ON UPDATE behavior is easy, but it will change for any UPDATE (CLI, phpMyAdmin, scripts), so it’s not selective.
  • Server-side triggers: keep the timestamp consistent regardless of client; or write a full audit row on INSERT/UPDATE/DELETE for a historical trail (who, when, old/new). Triggers are generally the best balance when you want database-enforced tracking.
  • Retrospective reconstruction: if you didn’t track changes, only server logs/backups (if available and enabled) can help — otherwise the past state can’t be reconstructed reliably.

Example trigger-based patterns (adapt to your schema):

DELIMITER $$
CREATE TRIGGER mytable_before_update
BEFORE UPDATE ON mytable
FOR EACH ROW
BEGIN
  SET NEW.updated_at = UTC_TIMESTAMP();
END$$
DELIMITER ;

A simple audit approach:

CREATE TABLE mytable_audit (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mytable_id INT,
  action ENUM('INSERT','UPDATE','DELETE'),
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  changed_by VARCHAR(128),
  old_values TEXT,
  new_values TEXT
);
-- then INSERT into this table from AFTER INSERT/UPDATE/DELETE triggers

Extra tips: use UTC timestamps to avoid timezone issues, index updated_at if you query "latest" often, and plan retention for audit tables (they grow fast). If you need tamper-resistant, compliance-grade logs, consider an audit plugin or external logging pipeline.

Recommended Answers

All 8 Replies

Member Avatar for smantscheff

Look in the log file.

Member Avatar for kartisathis

tanks fr ur reply bt i dn knw wr d log file located....

Look in the log file.

Member Avatar for kartisathis

ya u r rite i dn knw hw to spell ...
1st u understood my question i dont need any log files i jst need to fetch last updated row in a table, any solution plz... to

And obviously you don't know how to spell, too. So look in the manual. If you don't know where that is located, use a search engine or read here: http://dev.mysql.com/doc/refman/5.0/en/log-file-maintenance.html

Edited by kartisathis because: n/a
Member Avatar for smantscheff

Well, if your tables don't have timestamp fields, looking in the log files is the only general solution I know of.
And just for curiosity's sake: is it especially cool to write as you do? Or are you trying to make it as hard as possible for others to understand your desires?

Member Avatar for kartisathis

ya sorry man just for cool purpose only i reply like that and i found a solution for that the only way is to add one more column in table with default timestamp will solve this issue i think ....

this is my eg:
----------------
ALTER TABLE mytable
ADD lastmodified TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP;

Then, to find out the last updated row,

SELECT * FROM mytable ORDER BY lastmodified DESC LIMIT 1;

if u found any option other than this means it will be usefull for me...

Well, if your tables don't have timestamp fields, looking in the log files is the only general solution I know of.
And just for curiosity's sake: is it especially cool to write as you do? Or are you trying to make it as hard as possible for others to understand your desires?

Edited by kartisathis because: n/a
Member Avatar for smantscheff

In my experience timestamp fields are not really useful because you can access your database not only through your application but also through maintenance tools (like mysql command line, phpMyAdmin etc.). If you alter values with those, also the timestamp will be altered. That may be suitable for your purposes, for mine it has been more often not. I rather have a datetime field which is updated by the application explicitly.

Member Avatar for kartisathis

i dont understand will you please explain it briefly..

In my experience timestamp fields are not really useful because you can access your database not only through your application but also through maintenance tools (like mysql command line, phpMyAdmin etc.). If you alter values with those, also the timestamp will be altered. That may be suitable for your purposes, for mine it has been more often not. I rather have a datetime field which is updated by the application explicitly.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.