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

Reply via email to