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 name surname
---------- ---------- ---------- ----------
1 10 John Doe
2 20 Fred Flintstone
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 name surname
---------- ---------- ---------- ----------
1 10 John Doe
select rowid,* from oldContacts;
rowid ID name surname
---------- ---------- ---------- ----------
1 1 Fred Flintstone
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 name surname
---------- ---------- ---------- ----------
1 1 Fred Flintstone
2 2 John Doe
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]
