sqlite: update id automatically in a relational table

22 sec read

Let’s say we have three tables: user, paper, userPaperRelation.
user has columns id and username
paper has column id and title
userPaperRelation has columns userID and paperID. userID corresponds to the id in user table; paperID corresponds to id in paper table. This table is to tell who owns which papers.

Now, for some reason, a paper’s id is updated in the ‘paper’ table. Is there anyway so that the corresponding ‘paperID’ in ‘userPaperRelation’ table automatically update? The answer is yes. Use trigger.

CREATE TRIGGER updateTrigger UPDATE OF id ON paper
UPDATE userPaperRelation SET paperID=new.id WHERE paperID=old.id;


Want to receive new post notification? 有新文章通知我

How much money did I make from an app?

Undoubtedly some people are very successful in making money by developing a smartphone app. Back in 2012 I developed an app called “Handbook of Brain” which is a collected resources of brain anatomy, function and diseases. I put the app i
Xu Cui
27 sec read

Handy programs to visualize NIRS data (2): plotTopoMap

Often you need to view the spatial pattern of activation as in the example below. plotTopoMap allows you to do that. It probably only works for Hitachi devices where the spatial relationship between channels are known. In the above example, the activ
Xu Cui
37 sec read

Flash 3D video demo

Racer Ostrova Zombie
Xu Cui
0 sec read

2 Replies to “sqlite: update id automatically in a relational table”

  1. Thank you very much. You gave the most simple yet very clear example of the use of trigger on the entire web. Thanks a lot!

Leave a Reply

Your email address will not be published. Required fields are marked *