Bruce Clift wrote:
> 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?
>
You can use the last_insert_rowid() function to get the rowid (i.e. the
id field of a table with an integer primary key column) inside the trigger.
CREATE TABLE bar (id INTEGER PRIMARY KEY, ...);
CREATE TABLE foo (id INTEGER PRIMARY KEY,
bar_ref INTEGER REFERENCES bar(id));
CREATE TRIGGER new_bar after INSERT ON foo
BEGIN
INSERT INTO bar (...) VALUES(...);
UPDATE foo SET bar_ref = last_insert_rowid()
where id = new.id;
END
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
