GeniusConnect support forum
GeniusConnect support forum
Home | Profile | Register | Active Topics | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Database
 Views
 Using Views (database dependent)
 New Topic  Reply to Topic
 Printer Friendly
Author  Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic

Admin

Czech Republic
1677 Posts

Posted - 13 May 2004 :  00:12:29  Show Profile  Edit Topic  Reply with Quote  View user's IP address  Delete Topic
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..


Admin

Czech Republic
1677 Posts

Posted - 15 Dec 2006 :  01:57:50  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
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
Go to Top of Page

jgeiermann

USA
6 Posts

Posted - 10 Mar 2010 :  15:30:14  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
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.

Joe Geiermann
Go to Top of Page

Admin

Czech Republic
1677 Posts

Posted - 10 Mar 2010 :  16:40:28  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Great, Thanks
Go to Top of Page

Admin

Czech Republic
1677 Posts

Posted - 18 Jul 2016 :  16:45:57  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
SQL CREATE VIEW Statement
http://www.w3schools.com/sql/sql_view.asp
Go to Top of Page

Admin

Czech Republic
1677 Posts

Posted - 15 Jun 2018 :  13:16:34  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
SQL Server Triggers (also on Views)
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017
Go to Top of Page
   Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
GeniusConnect support forum © Copyright (C) 2003 - 2019, GeniusConnect, All Rights Reserved. Go To Top Of Page
Snitz Forums 2000