[SOLVED] SQLite3 issue when insert record lazarus / 16-Databases ---------------------------------------------------------------------- artem101 · 2022-03-31 19:47 Hello all! I have SQLite3 database with one table and view which adds one calculated field. For example: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TABLE t (    id   INTEGER PRIMARY KEY,    name VARCHAR); CREATE VIEW v AS    SELECT        *,        t.id || "-" || t.name AS calc_field    FROM t;  For allow data modification using view I added triggers for INSERT, UPDATE and DELETE operations: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TRIGGER del_trg    INSTEAD OF DELETE ON vBEGIN    DELETE FROM t WHERE id = OLD.id;END; CREATE TRIGGER ins_trg    INSTEAD OF INSERT ON vBEGIN    INSERT INTO t (name) VALUES (NEW.name);END; CREATE TRIGGER upd_trg    INSTEAD OF UPDATE ON vBEGIN    UPDATE t SET name = NEW.name    WHERE id = OLD.id;END;  After each insertion/updation calculated field in DBGrid needs to be updated. So I added pfRefreshOnUpdate and pfRefreshOnInsert in the ProviderFlags property on fields. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   object SQLQuery1: TSQLQuery    FieldDefs = <>    Database = SQLite3Connection1    Transaction = SQLTransaction1    SQL.Strings = (      'select * from v'    )    Options = [sqoRefreshUsingSelect]    Params = <>    Left = 56    Top = 136    object SQLQuery1id: TAutoIncField      DisplayLabel = 'ID'      DisplayWidth = 5      FieldKind = fkData      FieldName = 'id'      Index = 0      LookupCache = False      ProviderFlags = [pfInUpdate, pfInWhere, pfInKey, pfRefreshOnInsert, pfRefreshOnUpdate]      ReadOnly = False    Required = False    end    object SQLQuery1name: TStringField      DisplayLabel = 'Name'      DisplayWidth = 7      FieldKind = fkData    FieldName = 'name'      Index = 1      LookupCache = False      ProviderFlags = [pfInUpdate, pfInWhere, pfRefreshOnInsert, pfRefreshOnUpdate]      ReadOnly = False      Required = False    end    object SQLQuery1calc_field: TStringField      DisplayLabel = 'Calculated field'      DisplayWidth = 13      FieldKind = fkData    FieldName = 'calc_field'      Index = 2      LookupCache = False      ProviderFlags = [pfInUpdate, pfInWhere, pfRefreshOnInsert, pfRefreshOnUpdate]      ReadOnly = False      Required = False    end  end  But when I try to insert new record this error shown: SQLQuery1: Refresh SQL resulted in empty result set. I tried to find out more information from SQLite3 documentation and I think the reason is that last_insert_rowid() function works unexpected: QuoteIf an INSERT occurs within a trigger then this routine will return the rowid of the inserted row as long as the trigger is running. Once the trigger program ends, the value returned by this routine reverts to what it was before the trigger was fired. Any advices how to fix this? Thanks. ---------------------------------------------------------------------- af0815 · 2022-04-01 05:03 You make a insert on a view, not a table. And this view is working with triggers. IMHO not a ideal idea. Create a UPDATE, DELETE and INSERT SQL, which inserts/delete/update in the correct table and avoid the triggers in the DB. A writeable view is often a point of failure, and how should the query detect what the primary key is. If you write views or other statements avoid the using of '*'. This is for me the sign of no understanding. Normal i see this only in pupil projects. If i see a professional work with this, i try to avoid this 'Professional'. The reason is, when i write eg. a view and use *, i say to other, i have no knowledge about the structure of the DB and i am to lacy to write all the needed colums in the view. If later anybody try to change something it is hard to find out, what is to change. With the correct names, it is possible to find out what is to change. So with the * the DB is for me not maintable in future and a point of failure. ---------------------------------------------------------------------- Zvoni · 2022-04-01 06:24 As Andreas said: Don't use DML-Statements on Views. A View in itself is for "viewing" (as the Create-Statement implies since it's a SELECT). Something else regarding the "*" in the SELECT in context of Views: A View is precompiled in the Database. Meaning: Even if you have a View based on a SELECT * FROM, a later added Column to the table doesn't appear in the View. A View is a static snapshot of the Table-Structure at Creation-Time ---------------------------------------------------------------------- artem101 · 2022-04-01 09:31 Thank you both for answers. Quote from: af0815 on April 01, 2022, 07:03:15 amYou make a insert on a view, not a table. And this view is working with triggers. IMHO not a ideal idea. Quote from: Zvoni on April 01, 2022, 08:24:22 amAs Andreas said: Don't use DML-Statements on Views. Why? Many other databases allow to modify data recieved from views (directly, without using triggers). Could you explain your opinion? Quote from: af0815 on April 01, 2022, 07:03:15 amavoid the triggers in the DB But sometimes triggers may be usefull, for example, for automatic change record's update date. Quote from: af0815 on April 01, 2022, 07:03:15 amhow should the query detect what the primary key is. One TField object has pfInKey in ProviderFlags. ---------------------------------------------------------------------- af0815 · 2022-04-01 10:18 Quote from: artem101 on April 01, 2022, 11:31:15 amThank you both for answers. Quote from: af0815 on April 01, 2022, 07:03:15 amYou make a insert on a view, not a table. And this view is working with triggers. IMHO not a ideal idea. Quote from: Zvoni on April 01, 2022, 08:24:22 amAs Andreas said: Don't use DML-Statements on Views. Why? Many other databases allow to modify data recieved from views (directly, without using triggers). Could you explain your opinion? But the using of triggers can have side effects and the sqlDB drivers are not so stable to handle special usings. Actual reason, i have seen: SqlDb with MS-SQL if i use a stored procedure witch delivers a recordset doesnt work, if inside the SP another stored procedure is called witch delivers also a recordset. It doesnt work with SqlDB. Nope. With other Tools or Language it works. Period. My conclusion, not all possible with tools work with SqlDB like other Tools, so i have to make a compromise. ---------------------------------------------------------------------- Zvoni · 2022-04-01 10:29 Quote from: artem101 on April 01, 2022, 11:31:15 amWhy? Many other databases allow to modify data recieved from views (directly, without using triggers). Could you explain your opinion? Call it philosophy or personal preference. If something is called "View" then it's for "Viewing" which implies "Read-Only". Any DML i do i always do on Base-Tables, and that directly: No detour through Stored Procedures or whatever. Of course, if Master-Detail is in play, i adhere to the correct order of the Statements As for Triggers: i rarely use them, mainly for logging/auditing. In very rare cases i use a Trigger to update a Field in a Base-Table, if there was an DML-Statement in another table (think average price for items depending on adding/removing quantity from stock). BUT: If i use Triggers, it's 99% "Shoot and forget", so nothing expecting coming back If i need any information ("LastInsertedID" etc.) i grab it intentionally ---------------------------------------------------------------------- LacaK · 2022-04-02 06:15 If I understand correctly question, problem is that autoincrement ID field does NOT return correct new value (due to insert in trigger). In this case there subsequent refresh select can not succeed, because uses as primary key non-existent value for ID column (of just inserted row). Try using explicit specified InsertSQL property, where you use something like: INSERT INTO v(id,name) VALUES(:id,:name) RETURNING id; (this may be solves problem with correct id returned after insert) ---------------------------------------------------------------------- artem101 · 2022-04-05 20:19 Quote from: LacaK on April 02, 2022, 08:15:19 amTry using explicit specified InsertSQL property, where you use something like: INSERT INTO v(id,name) VALUES(:id,:name) RETURNING id; (this may be solves problem with correct id returned after insert) Thank for advise, but error is the same. When I run this query, NULL value returned. ---------------------------------------------------------------------- Sieben · 2022-04-05 20:31 If you drop the triggers and use a designated InsertSQL instead you should be able to obtain the last inserted RowID by TSQLite3Connection.GetInsertID. Works for me. ---------------------------------------------------------------------- artem101 · 2022-04-06 11:51 As af0815 and Zvoni advised, I removed insert trigger and wrote InsertSQL statement for making inserting to the table instead of view and now all works fine.