[Qt-interest] [MySQL] How to CREATE TRIGGER has multiple statements with QSqlQuery::exec()

[Me]

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.49-1ubuntu8.1 |
+-------------------+
1 row in set (0.00 sec)



On Thu, Jun 9, 2011 at 11:35 PM, Me <dooit.lee at gmail.com> wrote:

> ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple
> triggers with the same action time and event for one table'
> mysql>
>
>
> On Thu, Jun 9, 2011 at 11:08 PM, Me <dooit.lee at gmail.com> wrote:
>
>> How to make this work in my code, when I executed
>>         query.exec("delimiter //");
>> It reported
>>         delimiter //: FAILED
>>         query.lastQuery(): delimiter //
>> How to make this code work, I am really appreciate if anything would help.
>> ====================================================
>> if (!NDEBUG) {
>>         cerr << "delimiter //: ";
>>         cerr << (query.isActive() ? "SUCCESS" : "FAILED") << endl;
>>         cerr << "query.lastQuery(): "
>>              << query.lastQuery().toStdString() << endl;
>>     }
>>     query.exec("CREATE TRIGGER tmp_card BEFORE UPDATE ON card "
>>         "FOR EACH ROW "
>>         "BEGIN "
>>         "SET @sum = 100; "
>>         "END//");
>>     if (!NDEBUG) {
>>         cerr << "CREATE TRIGGER (for testing) tmp_card: ";
>>         cerr << (query.isActive() ? "SUCCESS" : "FAILED") << endl;
>>         cerr << "query.lastQuery(): "
>>              << query.lastQuery().toStdString() << endl;
>>     }
>>     query.exec("CREATE TRIGGER update_card BEFORE UPDATE "
>>        "ON card FOR EACH ROW "
>>        "BEGIN "
>>        "IF OLD.statusid <> NEW.statusid THEN (" +
>>        QString("IF NEW.statusid = %1 THEN ").arg(Status_Deactivate) +
>>        "    INSERT INTO log (cardid, transactionid, statusid, "
>>        "                     amount, balance) " +
>>        QString("VALUES (NEW.id, %1, %2, OLD.balance, 0); ").arg(
>>         Transaction_CancelAccount).arg(Status_Success) +
>>        "    SET NEW.balance = 0; " +
>>        QString("ELSEIF NEW.statusid = %1 THEN ").arg(Status_Online) +
>>        "    INSERT INTO log (cardid, transactionid, statusid, "
>>        "                     amount, balance) " +
>>        QString("VALUES (NEW.id, %1, %2, 0, NEW.balance); ").arg(
>>         Transaction_Login).arg(Status_Success) +
>>        QString("ELSEIF NEW.statusid = %1 THEN ").arg(Status_Offline) +
>>        "    INSERT INTO log (cardid, transactionid, statusid, "
>>        "                     amount, balance) " +
>>        QString("VALUES (NEW.id, %1, %2, 0, NEW.balance); ").arg(
>>         Transaction_Logout).arg(Status_Success) +
>>        QString("ELSEIF NEW.statusid = %1 THEN ").arg(Status_Reactivate) +
>>        "    INSERT INTO log (cardid, transactionid, statusid, "
>>        "                     amount, balance) " +
>>        QString("VALUES (NEW.id, %1, %2, 0, NEW.balance); ").arg(
>>         Transaction_Reactivate).arg(Status_Success) +
>>        QString("ELSEIF NEW.statusid = %1 THEN ").arg(Status_Depositing) +
>>        "    INSERT INTO log (cardid, transactionid, statusid, "
>>        "                     amount, balance) " +
>>        QString("VALUES (NEW.id, %1, %2, NEW.balance - OLD.balance, "
>>          " NEW.balance); ").arg(Transaction_Deposit).arg(Status_Success) +
>>        QString("SET NEW.statusid = %1; ").arg(Status_Online) +
>>        QString("ELSEIF NEW.statusid = %1 THEN ").arg(Status_Withdrawaling)
>>        + "INSERT INTO log (cardid, transactionid, statusid, "
>>                       "amount, balance) " +
>>        QString("VALUES (NEW.id, %1, %2, OLD.balance - NEW.balance, "
>>          " NEW.balance);
>> ").arg(Transaction_Withdrawal).arg(Status_Success)
>>        + QString("SET NEW.statusid = %1; ").arg(Status_Online) +
>>        QString("ELSEIF NEW.statusid = %1 THEN ").arg(Status_Transfering)
>>        + "INSERT INTO log (cardid, transactionid, statusid, "
>>                       "amount, balance) " +
>>        QString("VALUES (NEW.id, %1, %2, OLD.balance - NEW.balance, "
>>          " NEW.balance); ").arg(Transaction_Transfer).arg(Status_Success)
>>        + QString("SET NEW.statusid = %1; ").arg(Status_Online) +
>>        QString("ELSEIF NEW.statusid = %1 THEN "
>>               ).arg(Status_RecieveTransfering) +
>>        "INSERT INTO log (cardid, transactionid, statusid, "
>>                     "amount, balance) " +
>>        QString("VALUES (NEW.id, %1, %2, NEW.balance - OLD.balance, "
>>          " NEW.balance); ").arg(Transaction_RecieveTransfer
>>                          ).arg(Status_Success)
>>        + QString("SET NEW.statusid = %1; ").arg(Status_Online) +
>>        QString("ELSEIF NEW.statusid = %1 THEN "
>>               ).arg(Status_ChangePasswording) +
>>        "INSERT INTO log (cardid, transactionid, statusid, "
>>                     "amount, balance) " +
>>        QString("VALUES (NEW.id, %1, %2, NEW.balance - OLD.balance, "
>>          " NEW.balance); ").arg(Transaction_ChangePassword
>>                          ).arg(Status_Success)
>>        + QString("SET NEW.statusid = %1; ").arg(Status_Online) +
>>        "    END IF; "
>>        "  END IF;"
>>        "END//");
>>     if (!NDEBUG) {
>>         cerr << "CREATE TRIGGER update_card: ";
>>         cerr << (query.isActive() ? "SUCCESS" : "FAILED") << endl;
>>         if (!query.isActive())
>>             cerr << query.lastError().driverText().toStdString()
>>                  << endl;
>>         cerr << "query.lastQuery(): "
>>              << query.lastQuery().toStdString() << endl;
>>     }
>> ====================================================
>>
>> --
>> Victory Loves Preparation.
>> Me: http://about.me/dooit
>>
>
>
>
> --
> Victory Loves Preparation.
> Me: http://about.me/dooit
>



-- 
Victory Loves Preparation.
Me: http://about.me/dooit
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.qt-project.org/pipermail/qt-interest-old/attachments/20110609/aefd6162/attachment.html