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