I'm having difficulty writing a trigger in SQLite to do what I need to do. I have very little background in SQL, so I might well be missing something obvious. Any help would be greatly appreciated.
I have a table foo with a column that is a foreign key to table bar. Every time a row in foo is created, I want to automatically create a row in bar and include the reference to it in foo. I don't know how to write the trigger step such that it can insert the row in bar, get the value of the key column from the new row, and then update foo to point to it. The part that I don't know is how to refer to the newly-created row in the trigger step. Here is what I have: ---------- CREATE TABLE bar (id INTEGER PRIMARY KEY, ...); CREATE TABLE foo (..., bar_ref INTEGER REFERENCES foo(id)); CREATE TRIGGER new_bar INSERT ON foo FOR EACH ROW BEGIN INSERT INTO bar (...) VALUES(...); UPDATE foo SET new.bar_ref = ???; END ---------- Is there any way to refer to the id column of the new bar row? I think the usual approach for this problem would be for callers to create the bar row and then set the foo row themselves. That doesn't work in my case because I am adding bar to an existing system. Is there something different I could do? Maybe on table creation? Thanks! --Bruce _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
