At the bottom of http://www.sqlite.org/lang_createtrigger.html you can find the raise function info and decide which of ignore, rollback, abort or fail works best for you. Descriptions here: http://www.sqlite.org/lang_conflict.html
Insert the priority 0 row first, then do something like... create trigger pri0_stop_insert before insert on ColorScheme when new.PriorityLevel = 0 begin select raise(abort, 'No inserting priority 0 colors'); end; create trigger pri0_stop_update before update on ColorScheme when old.PriorityLevel = 0 begin select raise(abort, 'No updating priority 0 colors'); end; create trigger pri0_stop_delete before delete on ColorScheme when old.PriorityLevel = 0 begin select raise(abort, 'No deleting priority 0 colors'); end; -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: Thursday, October 12, 2017 3:01 AM To: 'SQLite mailing list' Subject: Re: [sqlite] [EXTERNAL] Trigger Question INSTEAD OF triggers are only allowed on views. From the documentation I would suggest using a BEFORE trigger and calling RAISE() in the trigger program. I expect RAISE(IGNORE) to silently abort the calling statement. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Stephen Chrzanowski Gesendet: Donnerstag, 12. Oktober 2017 08:36 An: General Discussion of SQLite Database <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] [sqlite] Trigger Question I'm setting up a resource string to drop, recreate, and repopulate a table to use as a type of default values for colors on the UI. Essentially a default set of priority levels and FG/BG colors associated with that priority color. The table schema is as such: CREATE TABLE [ColorScheme]( [PriorityLevel] INTEGER PRIMARY KEY ON CONFLICT REPLACE NOT NULL, [ForegroundColor] BIGINT NOT NULL DEFAULT 0, [BackgroundColor] BIGINT NOT NULL DEFAULT 12632256, [PriorityText] CHAR NOT NULL); I then insert a bunch of default values I want, looking like this: insert into ColorScheme values (0,000+000*256+000*65536,192+192*256+192*65536,'Unknown'); Essentially hard coding the default color when I display something on the UI that is of priority level 0. What my objective is, once I populate the initial set of data, I want to block any and all attempts that my code COULD do to update this exact row above. So on any insert, delete, or update, if PriorityLevel=0, I want a NOOP. This row does not update, it does not get deleted, this row essentially becomes bulletproof until I delete the table or trigger. The user is not informed of the inaction. Since I'm never going to have a priority of -1, I decided to try a trigger like this: Create Trigger ColorSchemeInsert instead of insert on ColorScheme for each row when NEW.PriorityLevel=0 begin delete from ColorScheme where PriorityLevel=-1; end; However, SQLite Expert is throwing an error saying "cannot create INSTEAD OF trigger on table: ColorScheme". If I remove the INSTEAD OF statement, the trigger is created without error, BUT, based on this ON CONFLICT REPLACE statement, the insert happens anyways. Thoughts? (For now, I'm not going to worry about the trigger, but it is something I want in) _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
