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