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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
