Thanks for this. I've not implemented this yet as I discovered a minor issue with the wrapper I'm using in that it doesn't like sending multiple SQL commands in one go, so I need to adapt my code, or adapt the wrapper to allow for multiple statements.
On Thu, Oct 12, 2017 at 9:38 AM, David Raymond <david.raym...@tomtom.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
