QSSqlQuery CREATE TRIGGER not working

Hallo

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

I try

Code:

---

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:

Code:

---

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

I think that before END You have to put char ;

Code:

---

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")

---

Quote:

---

Originally Posted by Lesiok

I think that before END You have to put char ;

Code:

---

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

Code:

---

"cannot commit - no transaction is active"

---

lg Chris

---

Added after 7 minutes:

Quote:

---

Originally Posted by ckvsoft

Hallo
Thx, but i try some thinks also with a semicolon ";" before END. But nothing works.
But the Error is

Code:

---

"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

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

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 ...

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

Quote:

---

Originally Posted by Lesiok

/.../ 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

Quote:

---

Originally Posted by smyk

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.