QSSqlQuery CREATE TRIGGER not working

[ckvsoft]

Hallo

I found some questions about it, but no realy solution.

I try


query.exec("CREATE TRIGGER UpdateCategoriesLastModifiedTime UPDATE OF categories_description, categories_title, categories_status ON categories BEGIN UPDATE categories SET last_modified=CURRENT_TIMESTAMP WHERE categories_id=categories_id END")
and get the Error:


incomplete input Der Befehl konnte nicht ausgeführt werden"
Function Name: static bool Database::open(bool) "cannot commit - no transaction is active Der Datensatz konnte nicht abgeholt werden"
Function Name: static bool Database::open(bool) "END"
Function Name: static bool Database::open(bool) "near \"END\": syntax error Der Befehl konnte nicht ausgeführt werden"


Can anyone help me?

lg Chris

[Lesiok]

I think that before END You have to put char ;

query.exec("CREATE TRIGGER UpdateCategoriesLastModifiedTime UPDATE OF categories_description, categories_title, categories_status ON categories "
"BEGIN "
" UPDATE categories SET last_modified=CURRENT_TIMESTAMP WHERE categories_id=categories_id; "
"END")

[ckvsoft]

I think that before END You have to put char ;

query.exec("CREATE TRIGGER UpdateCategoriesLastModifiedTime UPDATE OF categories_description, categories_title, categories_status ON categories "
"BEGIN "
" UPDATE categories SET last_modified=CURRENT_TIMESTAMP WHERE categories_id=categories_id; "
"END")


Hallo
Thx, but i try some thinks also with a semicolon ";" before END. But nothing works.
But the Error is
"cannot commit - no transaction is active"

lg Chris

Added after 7 minutes:


Hallo
Thx, but i try some thinks also with a semicolon ";" before END. But nothing works.
But the Error is
"cannot commit - no transaction is active"

lg Chris

Hallo

I see when i execute the query as multi-line the error ("cannot commit ...") is the same, but the TRIGGER was created now.

lg Chris

[Lesiok]

I think it's time to show more code and tell you what database it is.

[smyk]

Could it be, that Qt is trying to commit transaction (autocommit !?) where there is not an open transaction ? I'm not sure, if every DDL statement causes an implicit transaction ...

But as Lesiok already said, more details ...

[Lesiok]

Qt has nothing to do with it. Autocommit is made at the database level. The problem is with the code we can't see.

[smyk]

/.../ Autocommit is made at the database level.
Not necessarily. API's like ODBC or JDBC provides methods to manage transactions independent of database engine. I mean, transactions (begin, commit/rollback, savepoints, ...) are managed by client app, not by server. Only client knows, when to begin and when to commit a transaction ...

But perhaps this has nothing to do with the primary problem

[Lesiok]

Not necessarily. API's like ODBC or JDBC provides methods to manage transactions independent of database engine. I mean, transactions (begin, commit/rollback, savepoints, ...) are managed by client app, not by server. Only client knows, when to begin and when to commit a transaction ...

But perhaps this has nothing to do with the primary problem

If you manage the transaction via any API (calling begin / end transaction) then there is no autocommit. Probably the problem is that the author of the thread does not explicitly start the transaction and tries to complete it by calling the appropriate API function.
We won't think of anything without seeing a larger piece of code. You may also need to look into the database server logs.