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() ?
Thanks
Paolo
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
