sqlite: update id automatically in a relational table

October 23, 2008 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
BEGIN
UPDATE userPaperRelation SET paperID=new.id WHERE paperID=old.id;
END

写作助手，把中式英语变成专业英文

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
Jul 6, 2015 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
Apr 22, 2011 37 sec read

Flash 3D video demo

Racer Ostrova Zombie

Xu Cui
Feb 5, 2011 0 sec read

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

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!

Do you have a solution for the INSERT and DELETE too?