Ok, so now I have a new question.
Is there a way to prevent changes to the schema, while still allowing
inserts of new data to existing tables? I'd love to just prevent any
changes to sqlite_master for a given database connection. I tried adding
triggers to it similar to the ones I used below, but I get an error
"Error: cannot create trigger on system table".
Thanks!
Josh
Nevermind, I found the recursive_triggers PRAGMA...
Thanks!
Josh
Hello all,
I'm trying to create simple permissions for a table to prevent unwanted
modification of certain table rows. I'm brand new to Triggers, but I almost
have what I want working. Imagine I have a table t1 with some data, and a
permission integer. I have two triggers that prevent modification of
existing rows that have a certain permission level. So far, so good. I want
to allow INSERTs of new data, but prevent modification of existing data.
This works great except for one problem. You can get around the triggers by
doing a INSERT OR REPLACE and neither Trigger gets called.
So, if that table and triggers look like:
CREATE TABLE t1 ('name' varchar(40), 'perm' integer, PRIMARY KEY('name'));
CREATE TRIGGER trig1 BEFORE DELETE ON t1 WHEN (OLD.perm > 3)
BEGIN
SELECT RAISE(ABORT, 'Permission Error');
END;
CREATE TRIGGER trig2 BEFORE UPDATE ON t1 WHEN (OLD.perm > 3)
BEGIN
SELECT RAISE(ABORT, 'Permission Error');
END;
I create data like:
INSERT INTO t1 VALUES ('Normal data', 1);
INSERT INTO t1 VALUES ('Protected data', 5);
Then:
UPDATE t1 SET name='asdf' WHERE name='Protected data';
DELETE FROM t1 WHERE name='Protected data';
Will both fail as I want.
But I can do:
INSERT OR REPLACE INTO t1 VALUES('Protected data', 1);
DELETE FROM t1 WHERE name='Protected data';
And this will delete the row or allow modification. (Bad)
So... Is there any (easy) way to write an INSERT Trigger than just gets
called in the REPLACE case? Or maybe just ON CONFLICT? (I'm trying to avoid
writing an INSERT TRIGGER that does a SELECT of the entire table looking
for key conflicts AND I'm also trying to make the permission system as 'air
tight' as possible to prevent ways around it [It's not for a bank or
anything super crucial, but I would like it to be reasonably protected by
the permissions])
Thanks!
Josh
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
