A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in SQL statements the same way a table is referenced.
GeniusConnect can be used with Views instead of Table.
A view is used to do any or all of these functions:
Restrict a user to specific rows in a table. For example, allow an employee to see only the rows recording his or her work in a labor-tracking table.
Restrict a user to specific columns. For example, allow employees who do not work in payroll to see the name, office, work phone, and department columns in an employee table, but do not allow them to see any columns with salary information or personal information.
Join columns from multiple tables so that they look like a single table.Aggregate information instead of supplying details. For example, present the sum of a column, or the maximum or minimum value from a column.
Views are created by defining the SELECT statement that retrieves the data to be presented by the view. The data tables referenced by the SELECT statement are known as the base tables for the view. In this example, vCRMContacts selects data from two base tables to present a virtual table of commonly needed data:
CREATE VIEW vCRMContacts
AS
SELECT c.CompanyName, t.ContactName, t.ContactEmail,CompanyID,ContactID
FROM Company c, Contact t
where t.CompanyID=o.CompanyID
You can reference vCRMContacts in statements in the same way you would reference a table:
SELECT * FROM vCRMContacts
(can be the target of UPDATE, DELETE, or INSERT statements), as long as the modification affects only one of the base tables referenced by the view or if database supports INSTEAD OF triggers on VIEWS.
INSTEAD OF triggers can be defined on a view to specify the individual updates that must be performed against the base tables to support the INSERT, UPDATE, or DELETE statement. Also, partitioned views support INSERT, UPDATE, and DELETE statements that modify multiple member tables referenced by the view.
example:
CREATE TRIGGER iInsteadTrigger on vCRMContacts
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO Company (CompanyID, CompanyName)
SELECT CompanyID, CompanyName
FROM inserted
SET NOCOUNT OFF
INSERT INTO Contact(CompanyID,ContactID,ContactName,ContactEmail)
SELECT CompanyID,ContactID,ContactName,ContactEmail
FROM inserted
END
View from this example can be used with GeniusConnect.
PrimaryKey will be CompanyID,ContactID.
SQL Statements for generating new id's depends on datatype.
Examples:
select newid()
select max(CompanyID)+1 from Company
select dbo.myfunctionGetContactID..