I have the following example table (t1):
userIDEditIter int(10) unsigned primarykey autoincrement
userID int(10) unsigned
userName varchar
userType varchar
userLastEditDateTime DateTime
I would like to have the following view (v1):
userID int(10) unsigned
userName varchar
userType varchar
userLastEditDateTime DateTime
The difference between t1 and v1 would be that v1 would only show the
last record for each userID based on the userLastEdit. However, I would
like the functionality of the view to be such that whenever a query does
an update on the view, the view does an insert into the underlying table
with the lasted info for the particular userID. Thus, the query thinks
that the v1 is a table that only has the last information, while t1
contains the historical data, all transparent to the user. Triggers do
not appear to be implemented for views. While functions could allow for
some of the functionality desired they do not appear to be presentable
to users as tables.
Basically, I want users to be able to treat v1 as a table that can be
added to, deleted from, and updated, but still retain the historical
data in a way that is readily accessible to those with access to the
"backend" tables, in this case, t1. If someone could tell me the
technical term for this I'd be most appreciative.
The whole point of this is to make database access easy for those who
require access to certain information without burdening them with
business requirements that have no direct impact on their work.
Marc