Start a new topic

Cannot create trigger on SQLite DB

[This topic is migrated from our old forums. The original author name has been removed]

Hi there, I have a registered version of DBVis, currently on version 9.1.5 (most actual). I was playing around with DBVis to find out its capabilities on SQLite. Therefore I looked for some tutorials to get started and immediately found a problem, which I can't explain myself. I wanted to execute this command in an SQL Commander window: CREATE TRIGGER INSERT_T1_timeEnter AFTER INSERT on t1 BEGIN UPDATE T1 SET timeEnter=DATETIME('now') where rowid=new.rowid; END; The error message: 12:50:35 [CREATE - 0 row(s), 0.000 secs] [SQLITE_ERROR] SQL error or missing database (near "rowid": syntax error) Honestly, I can't find any kind of error in this statement. I compared it to a lot of other examples on the web, but everything I see is that the statement should be right. So, what's the deal? Regards, Thomas

[This reply is migrated from our old forums. The original author name has been removed]

Re: Cannot create trigger on SQLite DB
Well, I guess this is my fault. I once again read through the specs for this driver, it seems that it does NOT support triggers, unfortunately. I used the Firefox Addon "SQLite Manager" and was able to perform all operations without any problems. Really strange, that Xerial SQLite JDBC implemention doesn't support it in the current DbVis version ... Regards, Thomas
[This reply is migrated from our old forums.]

Re: Cannot create trigger on SQLite DB
Thomas, I was just testing this when you posted your second post. The driver does in fact support Triggers. The problem is that the trigger statement is a block of statements with embedded semi-colons. Semi-colon is also used by DbVisualizer to separate SQL statements in a script. So what is happening is that DbVisualizer pass each statement delimited by ; to the DB for execution. To fix this check the following document: http://confluence.dbvis.com/display/UG91/Executing+Complex+Statements I hope this helps. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Cannot create trigger on SQLite DB
Hi, thanks for providing me a solution. I really did not realize this, thanks for pointing it to me. I'll keep an eye on it in the future. Btw, I created a test trigger, however, it isn't shown in the objects list in the left pane. In SQLite Manager (FF addon) it does. This makes is somehow difficult to work with DbVis and SQLite databases. Do you think that this behaviour may change somewhere in the near future? Many thanks, Thomas
[This reply is migrated from our old forums.]

Re: Cannot create trigger on SQLite DB
Hi Thomas, You need to refresh the object tree, from the right click menu on the Triggers node, to see new objects created via the SQL Commander. Best Regards, Hans
[This reply is migrated from our old forums.]

Re: Cannot create trigger on SQLite DB
Hi Thomas, We just launched the BETA testing of the upcoming 9.5 version with a few enhancements for SQLite. It would be great if you can give it a test and verify. http://www.dbvis.com/download/beta/ For more information about the news in the BETA version: http://www.dbvis.com/doc/relnotes/index.jsp?showheader=false&showtoc=false&version=9.5-BETA We plan to go public with 9.5 in a few weeks. Regards Roger
Login or Signup to post a comment