title: syntax error with sqlite3_exec in C, but works fine in sqlite3 CLI program
body: <p>I am trying to develop a cross-platform C program that does simple SQLite database management. It simply creates some tables, triggers and then insert records periodically.</p>
  
  <p>The issue I encountered is that I got syntax error trying to create a trigger programmatically by calling sqlite3_exec with the SQL command directly. What's funny is that my program works fine in Windows. It creates/drops trigger successfully. However while running on Linux (Ubuntu) I got the following syntax error.</p>
  
  <pre><code>Info: connecting device...
  Info: Opened database ./test.db successfully
  Info: operation done successfully
  Error: Time:Fri Feb 24 17:22:29 2017 File:/home/stephen/code/IoTGW/azure-iot-gateway-sdk_0222/modules/sqlite/src/sqlite.c Func:sqlite_exec Line:315 SQL error: near ",": syntax error
  Error: Time:Fri Feb 24 17:22:29 2017 File:/home/stephen/code/IoTGW/azure-iot-gateway-sdk_0222/modules/sqlite/src/sqlite.c Func:sqlite_exec Line:317 Error Query: @CREATE TRIGGER MODBUS_size_control INSERT ON MODBUS WHEN (select count(*) from MODBUS)&gt;10
  BEGIN
  DELETE FROM MODBUS WHERE (ADDRESS,MAC,DATETIME) IN (SELECT ADDRESS,MAC,DATETIME FROM MODBUS ORDER BY DATETIME limit (select count(*) - 10 from MODBUS));
  END;@
  </code></pre>
  
  <p>*note that the <code>@</code> symbol is added just to make sure I didn't include some silly invisible value in the SQL command. <code>@</code> is not part of the SQL command executed by sqlite3_exec.</p>
  
  <p>Except than that, the other insert operations work just fine.</p>
  
  <p>Another weird thing is that I ran the exact same SQL command which caused the error in the sqlite3 shell program. It works just fine. </p>
  
  <p>After creating the trigger in sqlite3 shell. I ran my program again, it gave me a totally different error.</p>
  
  <pre><code>Error: Time:Fri Feb 24 17:52:49 2017 File:/home/stephen/code/IoTGW/azure-iot-gateway-sdk_0222/modules/sqlite/src/sqlite.c Func:sqlite_exec Line:315 SQL error: malformed database schema (MODBUS_size_control) - near ",": syntax error
  Error: Time:Fri Feb 24 17:52:49 2017 File:/home/stephen/code/IoTGW/azure-iot-gateway-sdk_0222/modules/sqlite/src/sqlite.c Func:sqlite_exec Line:317 Error Query: @CREATE TRIGGER MODBUS_size_control INSERT ON MODBUS WHEN (select count(*) from MODBUS)&gt;10
  BEGIN
  DELETE FROM MODBUS WHERE (ADDRESS,MAC,DATETIME) IN (SELECT ADDRESS,MAC,DATETIME FROM MODBUS ORDER BY DATETIME limit (select count(*) - 10 from MODBUS));
  END;@
  Error: Time:Fri Feb 24 17:52:51 2017 File:/home/stephen/code/IoTGW/azure-iot-gateway-sdk_0222/modules/sqlite/src/sqlite.c Func:sqlite_exec Line:315 SQL error: malformed database schema (MODBUS_size_control) - near ",": syntax error
  Error: Time:Fri Feb 24 17:52:51 2017 File:/home/stephen/code/IoTGW/azure-iot-gateway-sdk_0222/modules/sqlite/src/sqlite.c Func:sqlite_exec Line:317 Error Query: @INSERT INTO MODBUS(VALUE,ADDRESS,MAC,DATETIME) VALUES(09221,40002,'01:01:01:01:01:01','2017-02-24 17:52:51');INSERT INTO MODBUS(VALUE,ADDRESS,MAC,DATETIME) VALUES(29113,40001,'01:01:01:01:01:01','2017-02-24 17:52:51');@
  </code></pre>
  
  <p>As shown, even the previously working insert commands no longer work anymore. Seems that the trigger is causing all the problems. But I just don't see why. It works on Windows, works on Ubuntu in sqlite3 shell. Even if I insert records from sqlite3 shell after the trigger is created, it works fine.
  Please advise. Thanks in advance.</p>