StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
6298881
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
7
CommunityOwnedDate
CreationDate
2011-06-09T20:32:31.743
FavoriteCount
0
LastActivityDate
2016-10-31T05:50:42.653
LastEditDate
2016-10-31T05:50:42.653
LastEditorUserId
1402846
OwnerUserId
485534
ParentId
6296313
PostTypeId
2
Score
63
ViewCount
0
LastEditorDisplayName
text
Body
As a workaround, you could use the timestamp (old and new) for checking though, that one is not updated when there are no changes to the row. (Possibly that is the source for confusion? Because that one is also called 'on update' but is not executed when no change occurs) Changes within one second will then not execute that part of the trigger, but in some cases that could be fine (like when you have an application that rejects fast changes anyway.) For example, rather than <pre><code>IF NEW.a <> OLD.a or NEW.b <> OLD.b /* etc, all the way to NEW.z <> OLD.z */ THEN INSERT INTO bar (a, b) VALUES(NEW.a, NEW.b) ; END IF </code></pre> you could use <pre><code>IF NEW.ts <> OLD.ts THEN INSERT INTO bar (a, b) VALUES(NEW.a, NEW.b) ; END IF </code></pre> Then you don't have to change your trigger every time you update the scheme (the issue you mentioned in the question.) EDIT: Added full example <pre><code>create table foo (a INT, b INT, ts TIMESTAMP); create table bar (a INT, b INT); INSERT INTO foo (a,b) VALUES(1,1); INSERT INTO foo (a,b) VALUES(2,2); INSERT INTO foo (a,b) VALUES(3,3); DELIMITER /// CREATE TRIGGER ins_sum AFTER UPDATE ON foo FOR EACH ROW BEGIN IF NEW.ts <> OLD.ts THEN INSERT INTO bar (a, b) VALUES(NEW.a, NEW.b); END IF; END; /// DELIMITER ; select * from foo; +------+------+---------------------+ | a | b | ts | +------+------+---------------------+ | 1 | 1 | 2011-06-14 09:29:46 | | 2 | 2 | 2011-06-14 09:29:46 | | 3 | 3 | 2011-06-14 09:29:46 | +------+------+---------------------+ 3 rows in set (0.00 sec) -- UPDATE without change UPDATE foo SET b = 3 WHERE a = 3; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 -- the timestamo didnt change select * from foo WHERE a = 3; +------+------+---------------------+ | a | b | ts | +------+------+---------------------+ | 3 | 3 | 2011-06-14 09:29:46 | +------+------+---------------------+ 1 rows in set (0.00 sec) -- the trigger didn't run select * from bar; Empty set (0.00 sec) -- UPDATE with change UPDATE foo SET b = 4 WHERE a=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- the timestamp changed select * from foo; +------+------+---------------------+ | a | b | ts | +------+------+---------------------+ | 1 | 1 | 2011-06-14 09:29:46 | | 2 | 2 | 2011-06-14 09:29:46 | | 3 | 4 | 2011-06-14 09:34:59 | +------+------+---------------------+ 3 rows in set (0.00 sec) -- and the trigger ran select * from bar; +------+------+---------------------+ | a | b | ts | +------+------+---------------------+ | 3 | 4 | 2011-06-14 09:34:59 | +------+------+---------------------+ 1 row in set (0.00 sec) </code></pre> It is working because of mysql's behavior on handling timestamps. The time stamp is only updated if a change occured in the updates. Documentation is here: <a href="https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html" rel="noreferrer">https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html</a> <pre><code>desc foo; +-------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-----------------------------+ | a | int(11) | YES | | NULL | | | b | int(11) | YES | | NULL | | | ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------+-----------+------+-----+-------------------+-----------------------------+ </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POMySQL Trigger after update only if row has changed
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USPang
UserOwnerUserId
1. USInca
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POMySQL Trigger after update only if row has changed
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTAcceptedByOriginator
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTApproveEditSuggestion
3. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.