Creating a trigger not allowing a value that isn't in another column-CodePudding

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.

CodePudding user response：

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.