Administrator
  •  Admin
  • Advanced Member Topic Starter
2004-05-13T00:12:00Z
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


Views can be used to partition data across multiple databases.

Views can be updatable (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..
Administrator
  •  Admin
  • Advanced Member Topic Starter
2006-12-15T01:57:00Z
If your database has no support for VIEW triggers, you can use custom SQL command or Stored procedure to implement the insert/update/delete.

See: Assign Table->Options->SQL Commands
2010-03-10T15:30:00Z
This works great! We have 45 users contact folders that sync with a SQL database. I use a view to combine two tables together to supply the data for these contacts. This allows me to have some contact information that is the same across all Outlook users that have that contact and other information that is specific to the user. For instance the notes field of the a contact is specific to each user, so if user1 enters note of "This is my best friend" it only shows on her/his copy of the contact card. All other users do not see it and are free to enter their own notes. We also have categories, related contacts and nick name specific to each user. All other fields are synced to all users, so if an address is changed by anyone all user will receive the change.
Administrator
  •  Admin
  • Advanced Member Topic Starter
2010-03-10T16:40:00Z
Great, Thanks
Administrator
  •  Admin
  • Advanced Member Topic Starter
2016-07-18T16:45:00Z
Administrator
  •  Admin
  • Advanced Member Topic Starter
2018-06-15T13:16:00Z
Similar Topics