So What are Views and Triggers anyway

1
So What are Views and Triggers anyway?

• Much of the material presented in these slides
  was developed by Dr. Ramon Lawrence at the
  University of Iowa

2
Views

• A view is the dynamic result of a query over the
  base relations to produce another relation.
• It is considered virtual because it does not
  usually exist inside the database, but rather is
  calculated when needed.
• To the user, a view appears just like any other
  table and can be present in any SQL query where a
  table is present.
• A view may either be
• virtual - produced by a SQL query on demand.
• materialized - the view is stored as a derived
  table that is updated when the base relations are
  updated.

3
Creating Views

• Views are created using the CREATE VIEW command
• CREATE VIEW viewName (col1,col2,...,colN)
• AS selectStatement WITH CASCADEDLOCAL CHECK
  OPTION
• Notes
• Select statement can be any SQL query and is
  called the defining query of the view.
• It is possible to rename the columns when
  defining the view, otherwise they default to the
  names produced by the query.
• WITH CHECK OPTION is used to insure that if
  updates are performed through the view, the
  updated rows must satisfy the WHERE clause of the
  query.

4
Views Example

• Create a view that has only the employees of
  department 'D2'
• CREATE VIEW empD2
• AS SELECT FROM emp WHERE dno 'D2'
• Create a view that only shows the employee
  number, title, and name
• CREATE VIEW staff (Number,Name,Title)
• AS SELECT eno,ename,title FROM emp
• Note that the first example is a horizontal view
  because it only contains a subset of the tuples.
• The second example is a vertical view because it
  only contains a subset of the attributes.

5
Using views

• A view name may be used in exactly the same way
  as a table name in any SELECT query.
• One of the most important uses of views is in
  large multi-user systems, where they make it easy
  to control access to data for different types of
  users.
• You could create separate views even on an
  Employees table, and control access to them like
  this
• CREATE VIEW phone_view AS SELECT empFName,
  empLName, empPhone FROM Employees GRANT SELECT
  ON phone_view TO public
• CREATE VIEW job_view AS SELECT employeeID,
  empFName, empLName, jobTitle, managerID FROM
  Employees GRANT SELECT, UPDATE ON job_view TO
  managers
• CREATE VIEW pay_view AS SELECT employeeID,
  empFName, empLName, payRate FROM Employees GRANT
  SELECT, UPDATE ON pay_view TO payroll

6
Triggers

• One of the problems with assertions is that they
  may have to be checked on every database
  modification unless the DBMS is intelligent
  enough to know when they should be checked.
• A solution to this problem is to explicitly tell
  the DBMS what events are of interest and provide
  an action that should be performed when that
  event occurs.
• Triggers are used to perform actions when a
  defined event occurs. They are newly standardized
  in SQL3 but have been present in various DBMSs
  for some time.

7
Triggers Example

• Consider this situation where triggers are
  useful.
• The WorksOn relation has a foreign key to Emp
  (eno). If a user inserts a record in WorksOn and
  the employee does not exist, the insert fails.
• However with triggers, we can accept the
  insertion into WorksOn and then create a new
  record in Emp so that the foreign key constraint
  is not violated.

8
Example Trigger
9
Triggers Syntax

• CREATE TRIGGER
• BEFORE AFTER INSTEAD OF
• FOR EACH ROW
• WHEN ()
• Notes
• BEFORE, AFTER, INSTEAD OF indicate when a trigger
  is executed.
• is the events that the trigger will be
  executed for. It will be one of these events
• INSERT ON R
• DELETE ON R
• UPDATE OF A1,A2,..,An on R

10
Triggers Syntax - FOR EACH ROW

• There are two types of triggers
• row-level triggers that are executed for each row
  that is updated, deleted, or inserted.
• statement-level triggers that are only executed
  once per statement regardless of how many tuples
  are affected.
• Inserting the clause FOR EACH ROW indicates a
  row-level trigger (the default is a
  statement-level trigger).

11
Why Use Triggers?

• Triggers supplement the standard capabilities of
  a DBMS. For example, you can use triggers to
• Automatically generate derived column values
• Prevent invalid transactions
• Enforce complex security authorizations
• Enforce complex business rules
• Provide transparent event logging
• Gather statistics on table access
• Modify table data when statements are issued
  against views