Force Explicit Setting of Column Value with SQLite Trigger

Question

Is it possible to declare a trigger in SQLite3 which forces the user to explictly provide a value within an UPDATE statement?

Lets assume we have an Article table:

CREATE TABLE Article (
  Id           INTEGER  PRIMARY KEY,
  Title        TEXT NOT NULL UNIQUE,
  Content      TEXT,
  UserInserted TEXT NOT NULL,
  UserUpdated  TEXT
);

I can declare the following trigger which prohibits empty values for the column UserUpdated:

CREATE TRIGGER IF NOT EXISTS Trig_Article_BEFORE_UPDATE
  BEFORE UPDATE OF Title, Content ON Article
BEGIN
  SELECT
  CASE
    WHEN new.UserUpdated IS NULL THEN RAISE(ABORT, 'UserUpdated must not be NULL.')
    WHEN length(new.UserUpdated) = 0 THEN RAISE(ABORT, 'UserUpdated must not be NULL.')
  END;
END;

Insertion works as expected:

INSERT INTO Article(Title, Content, UserInserted) VALUES('Foo', '', '<user_A>');

Updating without providing a UserUpdated in the first place works as well:

UPDATE Article SET Content = 'Bar' WHERE Id = 1;
-- Error: UserUpdated must not be NULL.

UPDATE Article SET Content = 'Bar', UserUpdated = '' WHERE Id = 1;
-- Error: UserUpdated must not be NULL.

But once a UserUpdated has been set it is no longer required to provide the column explicitly.

UPDATE Article SET Content = 'Bar', UserUpdated = '<user_B>' WHERE Id = 1;
UPDATE Article SET Content = 'Foo Bar' WHERE Id = 1;
-- No error

Is there a way to declare a trigger so that the last statement throws an error as well?

Update 22.11.2019

Thanks to C Perkins' answer I came up with a solution using an extra column.

An extra column CurrentUser is added to Article:

CREATE TABLE Article (
  -- ...
  CurrentUser  TEXT
);

A BEFORE UPDATE trigger ensures that this column is set:

CREATE TRIGGER IF NOT EXISTS Trig_Article_BEFORE_UPDATE
  BEFORE UPDATE ON Article
  WHEN old.Title <> new.Title OR
       old.Content <> new.Content OR
       old.CurrentUser <> new.CurrentUser
BEGIN
  SELECT
  CASE
    WHEN new.CurrentUser IS NULL THEN RAISE(ABORT, 'CurrentUser must not be NULL.')
    WHEN length(new.CurrentUser) = 0 THEN RAISE(ABORT, 'CurrentUser must not be NULL.')
  END;
END;

An AFTER UPDATE Trigger (if CurrentUser is not null) copies the value from CurrentUser to UserUpdated and clears CurrentUser again.

CREATE TRIGGER IF NOT EXISTS Trig_Article_AFTER_UPDATE
  AFTER UPDATE ON Article
  WHEN new.CurrentUser IS NOT NULL
BEGIN
  UPDATE Article SET UserUpdated = new.CurrentUser, CurrentUser = NULL WHERE Id = new.Id;
END;

To prevent direct updates of UserUpdated another trigger is used:

CREATE TRIGGER IF NOT EXISTS Trig_Article_UserUpdated_BEFORE_UPDATE
  BEFORE UPDATE ON Article
  WHEN old.UserUpdated <> new.UserUpdated AND
       old.CurrentUser IS NULL
BEGIN
  SELECT RAISE(ABORT, 'You must not UPDATE UserUpdated.');
END;

After all I get the desired behaviour. Every time Content or Title are updated the Column CurrentUser has to be provided explicitly within the update statemant and UserUpdated reflects the last user who updated the values.

I'm fairly sure this isn't possible unless you add the requirement that the new `UserUpdated` value must be different from the current value. — Shawn, Nov 21 '19 at 15:46
For fields that can be null, expressions like `old.Content <> new.Content` are not sufficient. Ex: If either `old.Content` or `new.Content` contain null, then the result of `old.Content <> new.Content` is also null (even though they are not equivalent), so the condition fails. It should be `NOT ((column1 is null AND column2 is null) OR (column1 is not null AND column2 is not null AND column1 == column2))` or `ifnull(column1 <> column2, TRUE) AND NOT (column1 IS NULL AND column2 IS NULL)`. See https://stackoverflow.com/questions/1075142/how-to-compare-values-which-may-both-be-null-is-t-sql — C Perkins, Nov 23 '19 at 19:06
A couple notes about SO... Users are not notified when an answer is updated. You referenced my answer, but only by happen-chance did I come back to this question. To notify other users, either include a user tag in a comment (see comment help), like @urbanSoft, or upvote their answer. If you develop a solution that you want to share... even if it is based on another user's comments or answer, you should also post it as an answer, not an update to the question. It is okay to answer your own questions. That allows others to vote on and comment on your answer separately from the question. — C Perkins, Nov 23 '19 at 19:10

C Perkins · Answer 1 · 2019-11-21T17:40:51.893

Utilize another update-only column. Here's a quick outline of what would be involved:

Add an "update-only" column to the table: UserUpdateONLY
On the BEFORE UPDATE trigger, require that New.UserUpdateONLY IS NOT NULL AND length(New.UpdateUpdateONLY) != 0 and that New.UserUpdated == Old.UserUpdated OR (New.UserUpdated IS NULL AND Old.UserUpdated IS NULL) to avoid having contradiction of both columns with updated data (raise error if either condition is false).
On the INSTEAD OF UPDATE trigger, copy the value from the update-only column to the normal storage column: SET UserUpdateONLY = NULL, UserUpdated = NEW.UserUpdateONLY

The only possible problem is if NULL updates are allowed on the normal column, because then the obvious "trigger" value will not work in that case. If it might be a problem, instead store an unlikely value as the default for the UserUpdateONLY column, something like '<NOT VALID>' so that a new valid will always be detected.

Force Explicit Setting of Column Value with SQLite Trigger

Update 22.11.2019

1 Answers1