Re: [sqlite] Triggers & last_inserted_id

[Dennis Cote]

Pix wrote:
> Suppose I've some tables like these:
>
> CREATE TABLE contacts (ID, name, surname);
> CREATE TABLE oldContacts (ID, name, surname);
> CREATE TABLE messages (message, contactID, contactWasDeleted default
> NULL);
>
> I wrote a trigger similar to this:
>
> CREATE TRIGGER OnDeleteContact BEFORE DELETE ON Contacts
> FOR EACH ROW
> BEGIN
> INSERT INTO OldContacts (name, surname) VALUES (old.name,
> old.surname); UPDATE messages SET contactID = last_insert_rowid(),
> contactWasDeleted=true WHERE contactID = old.id AND
> contactWasDeleted=false; END;
>
> Actually, when I delete a row in Contacts, the trigger create a new
> row
> in OldContacts and updates the messages.contactID field...
> The matter is that "last_insert_rowid()" doesn't return the ID of the
> item inserted by the trigger but returns a previously created ID
> (or 0 if no item was inserted)
>
> I think that the problem is that last_insert_rowid() is evaluated
> BEFORE performing the "INSERT INTO etc..." statement...
>
> There is a way to have the right last_inserted_rowid() ?

Paolo,

Your problem arises because the ID columns are not the same as the rowid of
the records. In order for a column to be used as the rowid for a table, that
column must be declared with type of INTEGER PRIMARY KEY.

For your trigger to work as you want you need to change the definition of
the ID column in the oldContacts table to INTEGER PRIMARY KEY. This will
cause the ID values to be auto assigned and have the same vallues returned
by last_insert_rowid().

The following trace shows the correct operation. Note you can see the
difference between the rowid and your ID column in the dumps of the contacts
table (which does not have ID declared INTEGER PRIMARY KEY).

SQLite version 2.8.13
Enter ".help" for instructions
sqlite> .read test_lir.sql
CREATE TABLE contacts (ID, name, surname);
CREATE TABLE oldContacts (ID INTEGER PRIMARY KEY, name, surname);
CREATE TABLE messages (message, contactID, contactWasDeleted default 'F');

CREATE TRIGGER OnDeleteContact BEFORE DELETE ON Contacts
FOR EACH ROW
BEGIN
INSERT INTO OldContacts (name, surname) VALUES (old.name, old.surname);
UPDATE messages SET
contactID = last_insert_rowid(),
contactWasDeleted = 'T'
WHERE contactID = old.id AND contactWasDeleted = 'F';
END;

insert into contacts values (10, 'John', 'Doe');
insert into contacts values (20, 'Fred', 'Flintstone');

insert into messages values (101, 10, 'F');
insert into messages values (102, 20, 'F');

mode column
header on

select rowid,* from contacts;
rowid   ID  namesurname
--  --  --  --
1   10  JohnDoe
2   20  FredFlintstone
select rowid,* from oldContacts;
select rowid,* from messages;
rowid   message contactID   contactWasDeleted
--  --  --  -
1   101 10  F
2   102 20  F

delete from contacts where ID = 20;

select rowid,* from contacts;
rowid   ID  namesurname
--  --  --  --
1   10  JohnDoe
select rowid,* from oldContacts;
rowid   ID  namesurname
--  --  --  --
1   1   FredFlintstone
select rowid,* from messages;
rowid   message contactID   contactWasDeleted
--  --  --  -
1   101 10  F
2   102 1   T

delete from contacts where id = 10;

select rowid,* from contacts;
select rowid,* from oldContacts;
rowid   ID  namesurname
--  --  --  --
1   1   FredFlintstone
2   2   JohnDoe
select rowid,* from messages;
rowid   message contactID   contactWasDeleted
--  --  --  -
1   101 2   T
2   102 1   T
sqlite>

P.S. Note that SQL doesn't have a boolean data type with values true and
false. These are normally replaced with a single character code as I have
done above. The entire words 'True' and 'False' could be used just as well,
but they will simply take up more space in the database file.

I hope this helps.

Dennis Cote

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Triggers & last_inserted_id

[Darren Duncan]

At 10:53 AM +0200 6/3/04, Pix wrote:
No... I need the id of the row inserted by the TRIGGER... the 
trigger operations should be atomic, so no other db activity should 
be a problem...
I still don't think you need last_insert_id.  Try this in your trigger:
   INSERT INTO OldContacts (ID, name, surname) VALUES (old.ID, 
old.name, old.surname);
   UPDATE messages SET contactID = old.ID, contactWasDeleted=true

Of course, this version will be different, in that the rows in 
OldContacts will be exactly the same as those in contacts, including 
the ids, though the way I see it this conceptually is what should be 
happening.  Your older version, if it works, would not preserve all 
of the contact row in OldContacts, as the ID value may change.

-- Darren Duncan
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Triggers & last_inserted_id

[Darren Duncan]

At 5:38 PM +0200 6/2/04, Pix wrote:
There is a way to have the right last_inserted_rowid() ?
Don't use it.  Instead, try substituting the string "old.id" for 
"last_insert_rowid()".  You already know what the contact id is 
because its in "old.id".  Moreover, "last_insert_rowid()" is 
completely unreliable for what you are doing, since any number of 
other database activity could have happened between your inserting 
and deleting the contacts row. -- Darren Duncan

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]