It can make sense to insert data in order. There's lots of caveats to this though. If the data isn't frequently updated and if you're using certain types of tables or indexes (e.g. IOT, clustered indexes).
Being in order means that if you're doing a range scan of the ordered column (e.g. BETWEEN x AND Y
) then the data is more likely to be in a contiguous set of blocks.
Whether the data can be manipulated by a user depends on the rights he has. Rights to database objects like tables and views are given either directly to a user or via a role. Roles have the advantage that you can assign them to new users easily without having to deal with the details. I would suggest at least two roles: An admin role and a user role.
CREATE ROLE myApp_Admin;
CREATE ROLE myApp_User;
Assign rights to the roles:
GRANT SELECT, INSERT, UPDATE, DELETE ON state TO myApp_Admin;
GRANT SELECT ON state TO myApp_User;
-- ... grant rights to other tables and views here ...
Then, assign these roles to users
GRANT myApp_Admin TO invertigo;
GRANT myApp_User TO someoneElse;
Concern: data can be manipulated easily and modifying these records would break other parts of the software.
This should not be the case, if the database has been designed carefully. If you are using referential integrity, the users should not able manipulate the data in a way that corrupts the application. If you have a table employee
that refers to state
, then define a foreign key constraint on this column:
ALTER TABLE employee ADD CONSTRAINT FK_emp_sta_myApp
FOREIGN KEY (state_id)
REFERENCES state.id;
With this constraint it is not possible to delete a state or change its id
as long as it is referenced in employee
.
A completely different way of solving this problem, is to not have a lookup table (or view) at all in your db, but to keep the lookup information within your application (e.g. as embedded xml resource).
Yet another solution:
Create the table as another oracle owner and grant the select right to your usual application owner. Here you can then create a view.
CONNECT otherOwner;
CREATE TABLE state (...);
GRANT SELECT ON state TO appOwner WITH GRANT OPTION;
-- GRANT OPTION allows appOwner to grant this SELECT right to your users or roles.
CONNECT appOwner;
CREATE VIEW state AS
SELECT * FROM otherOwner.state
WITH READ ONLY;
or you could just create a synonym (as appOwner
):
CREATE SYNONYM state FOR otherOwner.state;
In both cases your users must not have access to otherOwner
.
Best Answer
This is not what triggers do -- they act on an event, such as a change to data.
Use DBMS_Scheduler to set up a repeating event.
Consider applying a function-based index to include only those rows that need updating, and make sure the query is constructed to be able to use it.