insert trigger insert value back

Question

I want to create a trigger on an insert action. This trigger should fire when a new row of clients is created. The trigger should create a new row in the accounts table and then insert the key value back into the row that fired the trigger.

clients:
_id|firstname|lastname|accountnr

accounts:
accountnr|balance

I tried creating a trigger with one insert and one update statement. The insert creates a new account in the account table. The problem is that the account table uses autoincrement, so I do not know the value of the newly created accountnr. That value I need to know in the update statement to update the accountnr of the people table to the accountnr in the account table.

I tried creating a trigger with one insert and one update statement. The insert creates a new account in the account table. The problem is that the account table uses autoincrement, so I do not know the value of the newly created accountnr. That value I need to know in the update statement to update the accountnr of the people table to the accountnr in the account table — hasdrubal

CL. CL. · Accepted Answer · 2013-05-01T06:21:13

That's what last_insert_rowid() is for:

CREATE TRIGGER new_account_for_new_client
AFTER INSERT ON clients
FOR EACH ROW
WHEN NEW.accountnr IS NULL
BEGIN
    INSERT INTO accounts(balance)
    VALUES(0);

    UPDATE clients
    SET accountnr = last_insert_rowid()
    WHERE _id = NEW._id;
END;

insert trigger insert value back

1 Answers