1

I have a question about a trigger that I would like to create and I am not sure how to do this.

I have a table, 2017_state_data, and it contains some financial metrics per state along with said state name. Now it's important that the state name is always correct (and that the state exists of course) and I would like to create a trigger where if the value of "State" does not coincide with the State names (from another table) then it should give a warning message along the lines of "Unknown state in entry". I am not entirely sure how to reference columns from a different table for use in a trigger and I was hoping someone could help me.

The trigger I have created is below, but this doesn't work and leaves me with an "incomplete input" error.

CREATE TRIGGER sureState
    BEFORE INSERT ON 2017_state_data
FOR EACH ROW
    WHEN NEW.State != (SELECT StateName FROM States)
        BEGIN SELECT RAISE(ABORT, "Unknown state in entry")
END;

Thanks in advance for all your help.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • I'm not SQLite savvy, but try changing the "!=" to "Not In". You are trying to compare a single value to the whole table. See if that works. – SS_DBA Oct 07 '21 at 21:46
  • Unfortunately I still get the same error. –  Oct 07 '21 at 21:48

1 Answers1

1

Your requirement has a name and it is Referential integrity.

All you have to do is define the column State of the table 2017_state_data so that it references the column StateName of the table States:

CREATE TABLE 2017_state_data (
  ........................
  State TEXT REFERENCES States(StateName),
  ........................
);

Now, if you enable Foreign Key Support (which is disabled by default) with:

PRAGMA foreign_keys = ON;

you make sure that no value that does not exist in StateName can be entered in State and there is no need for a trigger.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • This works perfectly. I did want to try and broaden my knowledge about trigger, that's why it was my first guess but this works even better. –  Oct 08 '21 at 08:52