Why trigger query fails in the sqlite with Qt?
- by dexterous_stranger
I am a beginner in the SQL. I am using SQLite, Qt - on embedded systems. I want to put a trigger here. The trigger is that whenever the primary key Id is greater than 32145, then channelNum=101 should be set. I want to set the attrib name - text also, but I got the compilation issue. I believe that the setting of trigger is the part of DDL - Data definition language. Please let me know that if I am wrong here. Here is my create db code. I get the sql query error. Also please do suggest how to set the text attrib = "COmedy".
/** associate db with query **/
QSqlQuery query ( m_demo_db );
/** Foreign keys are disabled by default in sqlite **/
/** Here is the pragma to turn them on first **/
query.exec("PRAGMA foreign_keys = ON;");
if ( false == query.exec())
{
qDebug()<<"Pragma failed";
}
/** Create Table for storing user preference LCN for DTT **/
qDebug()<<"Create Table postcode.db";
query.prepare(" CREATE TABLE dttServiceList (Id INTEGER PRIMARY KEY, attrib varchar(20), channelNum integer )" );
if ( false == query.exec())
{
qDebug()<<"Create dttServiceList table failed";
}
/** Try placing trigger here **/
triggerQuery = "CREATE TRIGGER upd_check BEFORE INSERT ON dttServiceList \
FOR EACH ROW \
BEGIN \
IF Id > 32145 THEN SET channelNum=101; \
END IF; \
END; ";
query.prepare(triggerQuery);
if ( false == query.exec())
{
qDebug()<<"Trigger failed !!";
qDebug() << query.lastError();
}
Also, how to set the text name in the trigger - I want to SET attrib = "Comedy".
