SQLite Forum

Create a trigger without a name, BUG?

Create a trigger without a name, BUG?

(1) By anonymous on 2020-05-07 09:17:31 [link]

I've accidentally forgot to specify the name of a trigger:


There did not popup any error message, and when I was testing, the trigger worked.
But then I wanted to change the behavior of the trigger, and realized that the
name was missing.

To figure out the name of the trigger, I issued:

  SELECT name FROM sqlite_master WHERE type='trigger'

The result: 'AFTER'

To me it looks like there is something wrong with the parser.

(2) By ddevienne on 2020-05-07 09:43:17 in reply to 1 [link]

There was a similar thread recently, I believe.

The short answer is that there's no *bug*. SQLite, for historical  
reasons often, and sometimes by-design (i.e. no accident), is very  
*lax* in what it allows, and unfortunately often silently ignores  
pieces of SQL that some would hope it'd error-out on.

Things have gotten better recently, e.g. with warnings for using double-quotes  
with string literals (generated *tons* of warnings in our logs recently,  
when we upgraded, because of course when something is wrong yet allowed  
it quickly permeates everywhere...), but some things cannot be fixed  
w/o breaking backward compatibility, like I suspect in this case.


(3) By Ryan Smith (cuz) on 2020-05-07 10:33:00 in reply to 1 [link]

The SQL:


Simply means: Create a Trigger named "AFTER" that gets called on UPDATE of "Proda_Page"....

So to the parser cannot read human intent, it can only read actual SQL, and if you put the word "AFTER" where it expects a name of a trigger, then it must assume your intent is to name that trigger "AFTER".

Unlike Dominique, I can't really see the "one would hope to error on" thing.  I'd like my parsers not to think for me and do what I say, not what it thinks I may have meant. If I really wanted to call my trigger so, why should the parser stop me?

This whole protecting people from themselves is Microsoft and Apple-ish nonsense that should be killed with fire. One designs things how you want it, and then tests if it is working, and if not, read the manual and debug. This is great for learning - you would never again make the mistake of misnaming a trigger, or at least, if your next trigger doesn't work, you'd immediately know where to check first. That is great, to my mind.

I do however agree (as I think Dominique specifically requested before, maybe others) that such things should be documented. (Perhaps the word I'm looking for is "documentationally mentioned"?).
There is indeed a pitfalls page in SQLite forum (I'm not sure if this one is mentioned on it) but I think it should gain a more prominent placement.

While on the subject, these three items I think should have prominent links on the main page of SQLite, AND on the new online forum page:
  - When To Use
  - How to Corrupt
  - SQLite common Pitfalls
Because, those 3 pages together answer 99% of questions posted here.


(4) By Richard Hipp (drh) on 2020-05-07 11:05:52 in reply to 1 [link]

See <https://www.sqlite.org/quirks.html#keywords_can_often_be_used_as_identifiers>

(5) By ddevienne on 2020-05-07 11:51:08 in reply to 3 [link]

> Unlike Dominique, I can't really see the "one would hope to error on" thing.

As Richard acknowledges himself, via the link he provided, the particular  
behavior here is both a *quirk* and *surprising*. Which amounts to *undesirable*  
in my book. Identifiers should **NOT** be allowed to be keywords, unless  
explicitly requested via double-quotes. This would catch this common enough  
mistake in triggers, that is even verbatim in the doc DRH linked to!

That it remains so for BC, I understand that. That there's no way to  
force SQLite to be stricter and less surprising less so... I'm well  
aware of the good reasons for this status quo to remain, but it doesn't  
mean I like it.

Regarding your `kill with fire`, that's undesirable hyperbole IMO.  
Linters, Assertions, Static Analysis, Fuzzers, etc... are all "nannies"  
as you seem to think, to prevent bugs for those who know humans make  
mistakes, and they don't need to be used by those that think so highly  
of themselves they think they don't need them. A safer and stricter mode  
for SQLite, catching the above *quirk*, would be an extremely welcome change.

(6) By Ryan Smith (cuz) on 2020-05-07 17:48:49 in reply to 5 [link]

I think the hyperbole is understated, if anything.

I think perhaps we are not talking about the same thing.

Assertions are self-checking by the programmer, not a nanny-state in any way and very necessary,
Static Analysis, Fuzzers and its ilk are all informative - tools to help you see the light - they are definitely not Nannies in any way.

You do see the difference between something "informing" me of an assumed stupid thing I did... and a compiler/query-planner prohibiting/breaking/erroring out based on the same assumption of the stupid thing I did - right?

One should be killed with fire, the other should be applauded - I myself have spent quite some effort making the schema-analyszer in SQLiteSpeed that informs people of just these kinds of naming (and other) possible problems, when they ASK it to do so. What it should never do, is prohibit it or error on it.

I hope that makes my belief more clear.

(7) By Keith Medcalf (kmedcalf) on 2020-05-07 19:19:10 in reply to 1 [link]

The syntax diagram for the CREATE TRIGGER statement can be found here <https://sqlite.org/lang_createtrigger.html>.  If you can trace your way through the diagram using your provided statement, then that statement is syntactically correct.  That you may have made a spelling or semantic error is **YOUR** problem and not SQLite3's problem.

Since the statement sample you have given **IS** indeed valid syntax, the problem is that **YOU** made an error.  It is not job of the parser to second-guess your clearly stated syntax free statement on the theory that **YOU** might be an idiot and not know what you are doing.

If you wish a product that does hand-holding, then use a product that does hand holding.  Microsoft and Apple are the two best known examples of the makers of such nanny products.

(8) By anonymous on 2020-05-08 05:32:41 in reply to 7

Oh, your right, I looked at this before, but I did not realize that the keywords BEFORE/AFTER/INSTEAD OF are optional, otherwise I would not have asked.