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

[Gopalakrishna Bhat]

http://dev.mysql.com/doc/refman/5.1/en/triggers.html

On 6/9/11, Me <dooit.lee at gmail.com> wrote:
> [SOLVED] this error means I have already have a trigger with the same table
> and same event, and I can only have one `BEFORE UPDATE' trigger on table
> card. I read this from http://dev.mysql.com/doc/refman/5.1/zh/triggers.html,
> but I couldn't found an english version. Anyway, really thank you all so
> much.
>
> 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
>


-- 
My blog http://gkbhat.blogspot.com