Introducción
Como se ha indicado en entrega anterior sobre las instrucciones relacionadas con Data Definition Language (DDL) relacionadas con CREATE. En esta ocasion hablaremos de las instrucciones CREATE VIEW y CREATE TRIGGER.
CREATE VIEW
El uso de esta instrucción creará una tabla virtual cuyo contenido (columnas y filas) se define por una consulta. Por lo tanto, permite crear una vista de los datos en una o más tablas de la base de datos.
La sintaxis básica es la siguiente:
CREATE VIEW [ schema_name . ] view_name
AS select_statement [;]
Donde:
Schema_name – Indica el nombre del esquema al que pertenecerá la vista.
View-name – Indica el nombre de la vista y deben seguir las reglas para los identificadores.
Select_statement - Es la declaración de la instrucción SELECT que define la vista. La declaración puede utilizar más de una tabla y otras vistas.
Es importante indicar que las columnas de la vista adquieren los mismos nombres que las columnas identificadas de la instrucción SELECT.
Es posible crear vistas que permitan la actualización de datos y la utilización de índices, llevando a cabo las opciones especificas al momento de crear la vista. Estas opciones de definición de la vista usando la sintaxis adecuada debe revisarse en la documentación correspondiente.
Ejemplo 1:
CREATE VIEW Employee_BirthDate
AS
SELECT FirstName, LastName, BirthDateFROM Employee;
En este ejemplo se define una vista de nombre Employee_BirthDate con tres columnas denomindas FirstName, LastName y BirthDate de la tabla Employee.
Ejemplo 2:
CREATE VIEW HR.VwHireDateAS
SELECT p.FirstName, p.LastName, e.EntityID, e.HireDateFROM HR.Employee eJOIN Employee.Person AS p ON e.EntityID = p.EntityID ;
GO
Se puede observar que se define una vista dentro del esquema HR denominada VwHireDate conteniendo 4 columnas denominadas FirstName, LastName, EntityId y HireDate, seleccionadas de las tablas siguientes; del esquema HR la tabla Employee y des esquema Employee la tabla Person, uniendo sobre la columna EntityID encontrada en ambas tablas. Recuerde que esta definición solo genera el objeto vista, no se ejecuta la consulta. El resultado de la consulta se ejecuta cuando se hace referencia a la vista.
Como se ha indicado, una vista es una consulta almacenada, de tal forma que para usar la vista, ésta se colocara en una consulta. como se indica a continuación:
SELECT *FROM HR.VwHireDate;
CREATE TRIGGER
Debe recordarse que un desencadenador es un tipo especial de procedimiento almacenado que se ejecuta automáticamente cuando ocurre un evento en la base de datos o en el servidor. Aquí es importante indicar que, principalmente se conocen los activadores DML que se ejecutan cuando un usuario intenta modificar datos a través de un evento generado por una instrucción del lenguaje de manipulación de datos (DML). Los eventos DML son declaraciones INSERT, UPDATE o DELETE en una tabla o vista. Estos activadores se activan cuando se activa cualquier evento válido, ya sea que las filas de la tabla se vean afectadas o no.
Por otro lado, se tienen los desencadenadores DDL que se ejecutan en respuesta a una variedad de eventos del lenguaje de definición de datos (DDL). Estos eventos corresponden principalmente a declaraciones CREATE, ALTER y DROP de Transact-SQL y a ciertos procedimientos almacenados del sistema que realizan operaciones similares a DDL.
Desencadenadores DML
Para los desencadenadores DML se tiene la siguiente sintaxis básica:
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table_name }
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement } [ ; ]
Donde:
schema_name - El nombre del esquema al que pertenecerá el desencadenador DML. Estos tienen como ámbito el esquema de la tabla o vista en la que se crean.
trigger_name - El nombre del desencadenante. Debe seguir las reglas para identificadores.
Table_name - La tabla en la que se ejecuta el desencadenador DML. Esta muchas veces se denomina tabla desencadenante. Especificar el nombre completo de la tabla es opcional. Es importante indicar que no puede definirse activadores DML en tablas temporales locales o globales.
FOR | AFTER - FOR o AFTER se utiliza para especificar que el activador DML se activará solo cuando todas las operaciones especificadas en la instrucción SQL aplicada en la tabla activadora se han iniciado correctamente. Todas las acciones en cascada referenciales y comprobaciones de restricciones también deben tener éxito antes de que se active este activador.
INSTEAD OF - Como máximo, puede definir un activador INSTEAD OF por instrucción INSERT, UPDATE o DELETE en una tabla o vista.
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } – Permite especificar las instrucciones de modificación de datos que activan el activador DML cuando se intenta en la tabla. Se requiere que se especifique al menos una opción. Tome en cuenta que puede utilizar cualquier combinación de estas opciones en cualquier orden para la definición del activador.
sql_statement – contiene las condiciones y acciones desencadenantes. Las condiciones de activación especifican criterios adicionales que determinan si los eventos DML probados provocan que se ejecuten las acciones de activación. Las acciones desencadenantes especificadas en las instrucciones Transact-SQL entran en vigor cuando se intenta la operación. Los desencadenadores pueden incluir cualquier número y tipo de instrucciones Transact-SQL, con excepciones. Recuérdese que un disparador se diseña para verificar o cambiar datos basándose en una modificación de datos de una tabla o una declaración de definición de objeto; no debería devolver datos al usuario. Las instrucciones Transact-SQL en un desencadenador frecuentemente incluyen lenguaje de control de flujo.
Finalmente, los activadores DML utilizan las tablas lógicas (conceptuales) que se identifican como deleted y inserted. Estas son estructuralmente similares a la tabla desencadenante, es decir, la tabla en la que se intenta la acción del usuario. Las tablas deleted y inserted contienen los valores antiguos o los nuevos valores de las filas que pueden manipularse o cambiarse mediante la acción del usuario.
Ejemplo 1:
CREATE TRIGGER NotifyActionON Sales.CustomerAFTER INSERT, UPDATEAS
RAISERROR ('Notify Customer Relations', 16, 10);
GO
En este ejemplo, se define un disparador de nombre NotifyAction para la tabla del esquema Sales de nombre Customer despues de una instrucción DML INSERT o UPDATE, al ser ejecutado se enviara un mensaje indicando el mensaje 'Notify Customer Relations'.
Ejemplo 2:
El siguiente disparador evita que se inserte una fila en la tabla Purchasing.PurchaseOrderHeader cuando la calificación crediticia del proveedor especificado se establece en 5 (por debajo del promedio). Este ejemplo esta obtenido de documentación de Microsoft SQL Server.
CREATE TRIGGER Purchasing.LowCredit
ON Purchasing.PurchaseOrderHeaderAFTER INSERTASIF (ROWCOUNT_BIG() = 0)RETURN;IF EXISTS (SELECT 1FROM inserted AS iJOIN Purchasing.Vendor AS vON v.BusinessEntityID = i.VendorIDWHERE v.CreditRating = 5)BEGIN
RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);ROLLBACK TRANSACTION;RETURN
END;
Se puede observar que se define un disparador dentro del equema Purchasing de nombre LowCredit, para la tabla del mismo esquema de nombre PurchaseOrderHeader que se ejecutara despues de llevar a cabo una instruccion DML INSERT. Hay que hacer notar que se hace uso de la tabla conceptual inserted, esta tabla conceptual contiene los valores insertados en la tabla. Asimismo, se observa que si se cumple la condición indicada se enviara un mensaje y la operación DML INSERT no se confirmará y se deshara.
Es importante mencionar que los activadores DML se utilizan frecuentemente para hacer cumplir las reglas comerciales y la integridad de los datos.
Desencadenadores DDL
Microsoft SQL Server permite proporcionar la integridad referencial declarativa (DRI) a través de las declaraciones ALTER TABLE y CREATE TABLE. Sin embargo, la DRI no proporciona integridad referencial entre las distintas bases de datos. La integridad referencial se refiere a las reglas sobre las relaciones entre las claves primaria y foránea de las tablas.
Generalmente, un disparador se crea en la actual base de datos donde se encuentre trabajando; sin embargo, el desencadenador puede hacer referencia a objetos fuera de la base de datos actual, indicando el nombre de la base de datos en la referencia.
Es importante indicar que, si se especifica el nombre del esquema del activador para calificar el activador, califique el nombre de la tabla de la misma manera.
Para los disparadores DDL se utiliza la siguiente sintaxis básica:
CREATE] TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement } [ ; ]
Donde:
Trigger_name: El nombre del desencadenante. Debe seguir las reglas para identificadores.
DATABASE – Especifica que el alcance del desencadenador DDL aplica a la base de datos actual. Si se especifica, el disparador se activa cada vez que ocurre un event_type o event_group en la base de datos actual.
ALL SERVER – Especifica que el alcance de un desencadenador DDL en el servidor actual. Si se especifica, el disparador se activa cada vez que event_type o event_group ocurre en cualquier lugar del servidor actual.
event_type - El nombre de un evento de lenguaje Transact-SQL que, después del lanzamiento, provoca que se active un desencadenador DDL. Los eventos válidos para desencadenadores DDL se enumeran en Eventos DDL, dentro de la documentación correspondiente.
event_group - El nombre de una agrupación predefinida de eventos del lenguaje Transact-SQL. El desencadenador DDL se activa después del lanzamiento de cualquier evento de lenguaje Transact-SQL que pertenezca a event_group. Los grupos de eventos válidos para desencadenadores DDL se enumeran en Grupos de eventos DDL, en la documentación correspondiente. Una vez que CREATE TRIGGER haya terminado de ejecutarse, event_group también actúa como una macro al agregar los tipos de eventos que cubre a la vista del catálogo sys.trigger_events.
sql_statement – contiene las condiciones y acciones desencadenantes. Los desencadenadores DDL capturan información sobre el evento desencadenante mediante la función EVENTDATA (Transact-SQL).
Los desencadenadores DDL, al igual que los desencadenadores estándar, inician procedimientos almacenados en respuesta a un evento. Se ejecutan principalmente en respuesta a declaraciones del lenguaje de definición de datos (DDL). Los tipos de declaraciones incluyen CREATE, ALTER, DROP, GRANT, DENY, REVOKE y UPDATE STATISTICS. Ciertos procedimientos almacenados del sistema que llevan a cabo operaciones similares a DDL también pueden activar desencadenadores DDL. Los desencadenadores DDL no se activan en respuesta a eventos que afectan a tablas temporales y procedimientos almacenados locales o globales.
Ejemplo:
CREATE TRIGGER safetyON DATABASEFOR DROP_VIEWASIF (@@ROWCOUNT = 0)
RETURN;
RAISERROR ('You must disable Trigger "safety" to remove views!', 10, 1)ROLLBACK
GO
En este ejemplo, se aprecia que se define un disparaor a nivel base de datos, de nombre safety para cuando se ejecute una instrucción DDL DROP VIEW, si se cumple la condición no se envia mensaje, en caso contrario, se envia el mensaje indicado. Este disparador impide que se eliminen las vistas de la base de datos, a menos que se elimine el disparador.
Conclusión
La creacion de vistas y disparadores en una base de datos a través de las instrucciones previamente indicadas es una de las actividades más importantes que se realiza cuando se crea o mantiene una base de datos. Recuerdese que los disparadores aplican principalmente en las tablas para ayudar en la consistencia de informción.
Es importante consultar la documentación relacionada con la sintaxis para la creación de las vistas, asi como la correspondiente a la creación de disparadores, para un mejor conocimiento de este tipo de objetos.