dimanche 15 mars 2015

SQLite: how to enable counting number of rows modified from trigger

is there any way to enable counting of rows that trigger modified in SQLite?


I know it is disabled http://ift.tt/18PmxYD and i understand why, but can i enable it somehow?



CREATE TABLE Users_data (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Deleted BOOLEAN DEFAULT (0),
Name STRING
);

CREATE VIEW Users AS
SELECT Id, Name
FROM Users_data
WHERE Deleted = 0;

CREATE TRIGGER UsersDelete2UsersData
INSTEAD OF DELETE
ON Users
FOR EACH ROW
BEGIN
UPDATE Users_data SET Deleted = 1 WHERE Id = OLD.Id;
END;

-- etc for insert & update


then delete from Users where Name like 'foo' /* doesnt even need 'Id = 1' */; works fine, but numbers of modified rows is, as documentation say, always zero.


(I cant modify my DAL to automatically add "where Deleted = 0", so backup plan is to have table Users_deleted and 'on delete' trigger on Users table without any view, but then i have to keep tracking FKs (for example, what to do when someone delete from FK table) and so on...)


Edit: Returned number is used for checking on database concurrency.


Aucun commentaire:

Enregistrer un commentaire