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

[Me]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.qt-project.org/pipermail/qt-interest-old/attachments/20110609/9a512051/attachment.html