Error: cannot create trigger on system table

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

Error: cannot create trigger on system table

petern
What are the drawbacks to allowing triggers on system tables?  Is this an
arbitrary restriction?

In the "big picture" overview, what would it take to get system table
triggers working after bypassing the error check in trigger.c below?

  /* Do not create a trigger on a system table */
  if( sqlite3StrNICmp(pTab->zName, "sqlite_", 7)==0 ){
    sqlite3ErrorMsg(pParse, "cannot create trigger on system table");
    goto trigger_cleanup;
  }
_______________________________________________
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: Error: cannot create trigger on system table

Richard Hipp-3
On 9/20/17, petern <[hidden email]> wrote:
> What are the drawbacks to allowing triggers on system tables?  Is this an
> arbitrary restriction?

System tables are sometimes modified directly, without going through
the usual DELETE/INSERT/UPDATE logic.  In order to support triggers on
system tables, logic to check for and invoke triggers would need to be
added to every place in the code that modifies system tables.  This is
doable in theory, but is error prone, generates a lot of extra code
that is rarely used but takes up memory space on embedded devices and
must still be maintained, and is of questionable utility.
--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Error: cannot create trigger on system table

petern
OK.  If system table triggers are generally not maintained, what is
simplest recommended way, by C API or other means, for the application to
be notified of non-specific schema changes that include creation/deletion
events? In the vein of the columns meta-data eval.c tester I posted, it
would be helpful if the application could be notified and automatically
refresh its snapshot when tables or views arrive or depart the schema.

Regarding that eval.c test example. What is the status of implementing the
small and sensible change for an optional row separator (third) parameter
in the eval.c extension released with SQLite?  I'd be happy to provide my
changes.  Let me know if you want them.

On Wed, Sep 20, 2017 at 3:20 AM, Richard Hipp <[hidden email]> wrote:

> On 9/20/17, petern <[hidden email]> wrote:
> > What are the drawbacks to allowing triggers on system tables?  Is this an
> > arbitrary restriction?
>
> System tables are sometimes modified directly, without going through
> the usual DELETE/INSERT/UPDATE logic.  In order to support triggers on
> system tables, logic to check for and invoke triggers would need to be
> added to every place in the code that modifies system tables.  This is
> doable in theory, but is error prone, generates a lot of extra code
> that is rarely used but takes up memory space on embedded devices and
> must still be maintained, and is of questionable utility.
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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: Error: cannot create trigger on system table

Simon Slavin-3


On 20 Sep 2017, at 6:55pm, petern <[hidden email]> wrote:

> OK.  If system table triggers are generally not maintained, what is
> simplest recommended way, by C API or other means, for the application to
> be notified of non-specific schema changes that include creation/deletion
> events?

https://sqlite.org/pragma.html#pragma_schema_version

Keep checking that and do something when it changes.

Simon.
_______________________________________________
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: Error: cannot create trigger on system table

petern
Instead of polling from a custom program, what would be nice is a feature
within shell.c that permits the user to specify pre-execute SQL which
optionally executes silently before every statement sent through the input
line.  Testing SCHEMA_VERSION for changes as you suggest (and many other
useful refresh type operations) could then be implemented without need of
custom application development.

Such a feature would simplify CPU efficient integration of a more lively
and au courant command line as other processes change the database.  A
post-execute SQL feature in shell.c would also have many live integration
uses.





On Wed, Sep 20, 2017 at 11:25 AM, Simon Slavin <[hidden email]> wrote:

>
>
> On 20 Sep 2017, at 6:55pm, petern <[hidden email]> wrote:
>
> > OK.  If system table triggers are generally not maintained, what is
> > simplest recommended way, by C API or other means, for the application to
> > be notified of non-specific schema changes that include creation/deletion
> > events?
>
> https://sqlite.org/pragma.html#pragma_schema_version
>
> Keep checking that and do something when it changes.
>
> Simon.
> _______________________________________________
> 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: Error: cannot create trigger on system table

Simon Slavin-3


On 20 Sep 2017, at 10:10pm, petern <[hidden email]> wrote:

> Instead of polling from a custom program, what would be nice is a feature
> within shell.c that permits the user to specify pre-execute SQL which
> optionally executes silently before every statement sent through the input
> line.

shell.c is part of the command-line shell.  It’s intended for testing, and for people who can’t write software.  Since you understand the sort of things you understand, you shouldn’t be using it in a production setting.  I’m going to dismiss this and tell you how to do it in your own software instead.

If you want to check the statements your own program is executing then you can use an authorizer.

<https://sqlite.org/c3ref/set_authorizer.html>

and check for certain action codes:

<https://sqlite.org/c3ref/c_alter_table.html>

This will tell you in some detail what kind of schema chance it taking place.  Or you can instead just check the code to see if it means a change to the schema.

Note that this covers only operations performed via your own connection, i.e. by that same program running on that same computer.  It cannot tell you what other programs accessing the same database are doing.

Simon.
_______________________________________________
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: Error: cannot create trigger on system table

Simon Slavin-3


On 20 Sep 2017, at 10:36pm, Simon Slavin <[hidden email]> wrote:

> This will tell you in some detail what kind of schema chance it taking place.

Grrrr.  That should be

"This will tell you in some detail what kind of schema change is taking place."

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users