updating a primary key in a trigger not possible?

[barbalex]

Let me explain my situation.

I have two tables: users and app_states.

app_states replaces a local state library. Thus it is used in many components. A typical way to fetch the state is:

const { results: appState } = useLiveQuery(
  db.app_states.liveUnique({
    where: { user_email: authUser?.email },
  }),
)

Due to https://github.com/electric-sql/electric/issues/1132 I had to make a user's email the primary key of the app_states table. Otherwise when fetching using liveFirst electric-sql made my components rerender perpetually.

But I have now run into a problem caused by this circumvention:

To ensure the email is always correct I use this trigger:

CREATE TRIGGER IF NOT EXISTS users_app_states_email_trigger
  AFTER UPDATE OF email ON users
BEGIN
  UPDATE app_states SET user_email = NEW.email
  WHERE user_id = NEW.user_id;
END;

The problem is that when this runs, the following error occurs:

Error: cannot rollback - no transaction is active

I also tried to: 1. create a new app_state using the NEW.xxx data, then deleting the old one inside the trigger. Same error.

So it seems that updating a primary key in a trigger is not possible? Is this a problem in sqlite or in electric-sql? (I realize that it is a bit weird to update a primary key. But hey, this is only the app state row, so not a data integrity deal. And it's just because of the liveFirst rerender issue)

A different method would be to change the user_email to reference user.email with on update update. But that is not allowed in electric-sql (yet) as far as I know.

[linear[bot]]

VAX-1776 updating a primary key in a trigger not possible?

[barbalex]

Closing this not because setting a primary key works but .liveFirst seems to work when using an index on the where clause column, so I could solve my issue differently and I would prefer you guys to stay focused on implementing new features for now 😄

[barbalex]

In case someone else finds this: the docs explicitely say, that primary keys can not be updated: https://electric-sql.com/docs/usage/data-modelling/constraints#referential-integrity:

To preserve referential integrity Electric prevents updates to a table's primary keys.