Trigger Question

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Trigger Question

Stephen Chrzanowski
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Trigger Question

Hick Gunter
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:[hidden email]] Im Auftrag von Stephen Chrzanowski
Gesendet: Donnerstag, 12. Oktober 2017 08:36
An: General Discussion of SQLite Database <[hidden email]>
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Trigger Question

David Raymond
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:[hidden email]] 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:[hidden email]] Im Auftrag von Stephen Chrzanowski
Gesendet: Donnerstag, 12. Oktober 2017 08:36
An: General Discussion of SQLite Database <[hidden email]>
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Trigger Question

Stephen Chrzanowski
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 <[hidden email]>
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:[hidden email]]
> 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:[hidden email]]
> 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
> [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users