DDL - Vistes i DML

Una Vista simplifica l'accés a certes dades per alguns usuaris, però, algunes vistes també permeten als usuaris realitzar modificacions en les taules adjacents.

Tot DBA ha de mantenir la integritat de la BD, i per tant, pot posar certes restriccions en determinades vistes.

En vistes simples podem realitzar operacions de INSERT, UPDATE i DELETE, però, tots els canvis són possibles?

La vista creada a continuació, view_dept_50, s'ha creat pels caps del departament 50.

La intenció d'aquesta vista és permetre als caps del departament 50 veure la informació dels seus empleats.

CREATE VIEW view_dept50 AS
    SELECT department_id, employee_id, first_name, last_name, salary
    FROM copy_employees
    WHERE department_id = 50;

SELECT * FROM view_dept50;

Possible sortida a la SELECT anterior

+---------------+-------------+------------+-----------+---------+
| department_id | employee_id | first_name | last_name | salary  |
+---------------+-------------+------------+-----------+---------+
|            50 |         124 | Kevin      | Mourgos   | 5800.00 |
|            50 |         141 | Trenna     | Rajs      | 3500.00 |
|            50 |         142 | Curtis     | Davies    | 3100.00 |
|            50 |         143 | Randall    | Matos     | 2600.00 |
|            50 |         144 | Peter      | Vargas    | 2500.00 |
+---------------+-------------+------------+-----------+---------+

A la vista anterior podem INSERIR, ACTUALITZAR i SUPRIMIR informació de totes les files de la vista, fins i tot encara que el resultat sigui que una fila ja no formi parte de la vista; per exemple, si modifiquem el department_id.

Potser això no és el que el DBA volia al crear la vista.

Per a controlar l'accés a les dades, podem afegir la opció WITH CHECK OPTION a la instrucció CREATE VIEW.

Vistes amb WITH CHECK OPTION

Podem modificar el departament d'un empleat amb la vista i aquest no apareixerà més a la vista. El sistema ens deixa.

START TRANSACTION;
UPDATE view_dept50 SET department_id = 90
       WHERE employee_id = 124;
SELECT * FROM view_dept50;
SELECT * FROM employees WHERE employee_id = 124;
ROLLBACK;

+-------------+-----------+----------+---------+-------+
|department_id|employee_id|first_name|last_name|salary |
+-------------+-----------+----------+---------+-------+
|           50|        141|Trenna    | Rajs    |3500.00|
|           50|        142|Curtis    | Davies  |3100.00|
|           50|        143|Randall   | Matos   |2600.00|
|           50|        144|Peter     | Vargas  |2500.00|
+-------------+-----------+----------+---------+-------+
+-------------+-----------+----------+---------+-------+---+
|department_id|employee_id|first_name|last_name|salary |***+
+-------------+-----------+----------+---------+-------+---+
|           90|        124|Kevin     |Mourgos  |5800.00|***+
+-------------+-----------+----------+---------+-------+---+

Hem pogut modificar el codi de departament de l'empleat i aquest ha desaparegut de la vista però no de la taula.

I si creem la vista amb la opció WITH CHECK OPTION?

CREATE OR REPLACE VIEW view_dept50 AS
    SELECT department_id, employee_id, first_name, last_name, salary
    FROM copy_employees
    WHERE department_id = 50
WITH CHECK OPTION;

Aquesta vegada NO podem modificar el departament d'un empleat amb la vista ja que aquest no compliria la condició per continuar a la vista.

START TRANSACTION;
UPDATE view_dept50 SET department_id = 90
       WHERE employee_id = 124;
ERROR 1369 (44000): CHECK OPTION failed `empresa`.`view_dept50`
ROLLBACK;

Hem obtingut un error prou clarificador.

Restriccions de DML

Les vistes simples i complexes es dfierencien a l'hora de permetre operacions DML.

A les vistes simples podem realitzar operacions DML. Sempre que no es trenqui una regla de NOT NULL i d'UNIQUE.

A les vistes complexes, les operacions DML no sempre estan permeses.

Cal tenir en compte les tres regles següents quan realitzem operacions DML en vistes.

• No podem eliminar una fila des d'una taula base subjacent si la vista conté quelcom de:

  • Funcions de grup

  • Una clàusula GROUP BY

  • La paraula clau DISTINCT

• No podem modificar dades d'una vista si aquesta conté quelcom de:

  • Funcions de grup

  • Una clàusula GROUP BY

  • La paraula clau DISTINCT

  • Columnes definides amb expressions

• No podem afegir dades a una vista si aquesta:

  • Inclou funcions de grup

  • Inclou una clàusula GROUP BY

  • Inclou la paraula clau DISTINCT

  • Inclou columnes definides amb expressions

  • No inclou columnes NOT NULL a les taules base