Thank you both for the thoughtful responses. Comments:
Re: NEW.* -- Thanks Igor for pointing out that my assumptions were totally
off base. Assumptions are tricky things!
Re: "Why can't the trigger call myFunc(new.colA, new.colB)" -- that would
definitely work, but I'd like to make the trigger independent from the
table schema, such that I could add this trigger to a table without even
knowing its schema. The ultimate goal is to just automatically add it to
every table in the database, and thereby monitor all changes to all rows on
all tables -- without needing to hand-roll a bunch of triggers for each.
PRAGMA table_info() - Hm, that's a very clever technique. My original goal
was to create a reusable trigger query that can be applied verbatim to any
table, that will pass all columns to a function for each row change. It's
sounding like this isn't possible, unfortunately. But with your trick, I
could at least programmatically generate the appropriate trigger query as
follow:
1) Get a list of all tables
2) For each table, get a list of all columns with PRAGMA table_info()
3) Assemble and execute the appropriate query for each table to create a
comprehensive trigger.
Cool, thanks for the helpful tips!
-david
On Fri, Jan 30, 2015 at 9:31 PM, Tristan Van Berkom <
tris...@upstairslabs.com> wrote:
> On Sat, 2015-01-31 at 00:04 -0500, Igor Tandetnik wrote:
> > On 1/30/2015 10:44 PM, David Barrett wrote:
> > > Is it possible to create a trigger that calls a custom function and
> passes
> > > in NEW.*?
> >
> > Not literally NEW.* . You'll have to spell out individual columns as
> > parameters.
> >
> > > 2) I'm *assuming* if you pass a "*" into that function, it'll just call
> > > that function with however many columns are available.
> >
> > Your assumption is incorrect. If I recall correctly, the syntax
> > "myFunc(*)" is accepted, and is equivalent to "myFunc()" - that is, no
> > parameters are passed. Pretty much the only reason to allow this syntax
> > is to accept "count(*)".
> >
> > > 3) It seems that there is a way to create a custom trigger that has no
> > > byproduct but to call SELECT. The only reason I can imagine you'd
> want to
> > > do that is to call a custom function. But can I call that function
> with
> > > all columns using a *? (I can't quite figure this out from the docs
> alone.)
> >
> > Well, you could have tested it, and discovered it didn't work. You don't
> > even need a custom function, you could have used a built-in one.
> >
> > > SELECT myFunc( NEW.* );
> >
> > That would produce a syntax error.
> >
> > > Are these assumptions correct, and should the above generally work?
> >
> > No, and no.
> >
> > > My
> > > goal is to execute myFunc() every time there's an INSERT/UPDATE/DELETE
> on a
> > > given table -- but I want myFunc() to be reusable and not need to know
> the
> > > structure of the table it's being called on.
> >
> > I'm not sure how the necessity of myFunc(NEW.*) syntax follows from
> > this. Why can't the trigger call myFunc(new.colA, new.colB)?
> >
> > You can write a variadic custom function (one that can be called with an
> > arbitrary number of arguments), if that's what you are asking.
>
> Additional note,
>
> In order to generate queries on tables for which you dont know their
> structure (I've found this particularly useful in dynamic schema
> upgrades), you might find this useful:
>
> PRAGMA table_info ('table_name')
>
> This part should help you to generate a query which passes all the
> columns of a given table to myFunc()
>
> Cheers,
> -Tristan
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
