TRIGGER-VIEW-BASE DE DATOS CON DISPARADORES

BASE DE DATOS

1. Triggers Crear, modificar y eliminar desencadenadores

2. Trigger Un Trigger (en español, disparador ) es un objeto de base de datos que ejecuta automáticamente un conjunto de instrucciones SQL cuando ocurre un evento específico en una tabla, como: INSERT → cuando se inserta un nuevo registro UPDATE → cuando se modifica un registro existente DELETE → cuando se elimina un registro

3. Tipos de triggers DML (Data Manipulation Language ): Se activan por eventos de manipulación de datos como INSERT, UPDATE o DELETE. DDL (Data Definition Language ): Se activan por comandos de definición de datos como CREATE, ALTER o DROP. De inicio de sesión: Se activan cuando un usuario inicia sesión en la base de datos.

4. Desencadenadores DML El desencadenador DML es un tipo especial de procedimiento almacenado que surte efecto automáticamente cuando se produce un evento de lenguaje de manipulación de datos (DML) que afecta a la tabla o vista definida en el desencadenador. Los eventos DML incluyen las instrucciones INSERT, las instrucciones UPDATE, o las instrucciones DELETE.

5. Los desencadenadores DML pueden usarse para aplicar reglas de negocios y la integridad de datos, consultar otras tablas e incluir instrucciones Transact -SQL complejas. El desencadenador y la instrucción que lo activa se tratan como una sola transacción, que puede revertirse desde el desencadenador. Si se detecta un error grave (por ejemplo, no hay suficiente espacio en disco), se revierte automáticamente toda la transacción

6. Está definido para una tabla o vista específica y se crea para conservar la integridad referencial y la coherencia entre distintas tablas. Un trigger se asocia a un evento (inserción, actualización o borrado) sobre una tabla. S i se intenta modificar datos de una tabla en la que se definió un trigger para alguna de estas acciones (inserción, actualización y eliminación), el trigger se ejecuta (se dispara) en forma automática.

7. La diferencia con los procedimientos almacenados del sistema es que los triggers : No pueden ser invocados directamente; al intentar modificar los datos de una tabla para la que se ha definido un disparador, el disparador se ejecuta automáticamente. No reciben y retornan parámetros. Son apropiados para mantener la integridad de los datos, no para obtener resultados de consultas.

8. Una tabla puede contener varios trigger para un mismo evento y un trigger puede activarse para varios eventos. Los disparadores se ejecutan después de la ejecución de una instrucción " insert ", " update " o " delete " en la tabla en la que fueron definidos. Las restricciones se comprueban antes de la ejecución de la instrucción. Por lo tanto, las restricciones se comprueban primero, si se infringe alguna restricción, el desencadenador no llega a ejecutarse.

9. Para crear un Trigger debemos emplear la sentencia CREATE TRIGGER. Sintaxis: CREATE TRIGGER < nombre_trigger > ON < nombre_tabla > FOR <evento (INSERT, UPDATE o DELETE)> AS <Sentencias del trigger >

10. Para modificar un Trigger debemos emplear la sentencia ALTER TRIGGER ALTER TRIGGER < nombre_trigger > ON < nombre_tabla > FOR <evento (INSERT, UPDATE o DELETE)> AS -Sentencias del procedimiento

11. Para eliminar un Trigger debemos emplear la sentencia DROP TRIGGER. DROP TRIGGER < nombre_trigger >

12. EJEMPLO:

13. USE AdventureWorks2022; GO CREATE TRIGGER NewPODetail3 ON Purchasing.PurchaseOrderDetail FOR INSERT AS IF @@ROWCOUNT = 1 BEGIN UPDATE Purchasing.PurchaseOrderHeader SET SubTotal = SubTotal + LineTotal FROM inserted WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID ; END ELSE BEGIN UPDATE Purchasing.PurchaseOrderHeader SET SubTotal = SubTotal + (SELECT SUM( LineTotal ) FROM inserted WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID ) WHERE PurchaseOrderHeader.PurchaseOrderID IN (SELECT PurchaseOrderID FROM inserted ); END;

14. Trigger para registrar eliminaciones en SalesOrderDetailGuarda un registro cuando se elimine un detalle de orden. CREATE TRIGGER trg_AfterDelete_SalesOrderDetail ON SalesLT.SalesOrderDetail AFTER DELETE AS BEGIN INSERT INTO ErrorLog ( ErrorTime , ErrorMessage ) SELECT GETDATE(), CONCAT('Se eliminó el detalle con ID: ', d.SalesOrderDetailID ) FROM deleted d; END; GO

15. Trigger para actualizar el total de la ordenActualiza el campo SubTotal en SalesOrderHeader cada vez que se inserta o elimina un detalle.

16. CREATE TRIGGER trg_UpdateOrderTotal ON SalesLT.SalesOrderDetail AFTER INSERT, DELETE AS BEGIN UPDATE H SET SubTotal = ( SELECT SUM( LineTotal ) FROM SalesLT.SalesOrderDetail WHERE SalesOrderID = H.SalesOrderID ) FROM SalesLT.SalesOrderHeader H WHERE H.SalesOrderID IN ( SELECT SalesOrderID FROM inserted UNION SELECT SalesOrderID FROM deleted ); END; GO

17. TRIGGER (INSTEAD OF DELETE) Evita eliminar clientes si tienen órdenes asociadas.

18. CREATE TRIGGER trg_PrevenirEliminacionCliente ON SalesLT.Customer INSTEAD OF DELETE AS BEGIN IF EXISTS ( SELECT 1 FROM SalesLT.SalesOrderHeader AS H INNER JOIN deleted AS D ON H.CustomerID = D.CustomerID ) BEGIN RAISERROR('No se puede eliminar el cliente porque tiene órdenes registradas.', 16, 1); END ELSE BEGIN DELETE FROM SalesLT.Customer WHERE CustomerID IN (SELECT CustomerID FROM deleted ); END END;

19. PRUEBA : DELETE FROM SalesLT.Customer WHERE CustomerID = 1;

20. TRIGGER (AFTER INSERT) Registra en una tabla de auditoría cada vez que se inserta una nueva orden.

21. Primero creamos la tabla de auditoría: CREATE TABLE SalesLT.AuditoriaOrdenes ( AuditID INT IDENTITY PRIMARY KEY, SalesOrderID INT, CustomerID INT, OrderDate DATETIME, Usuario NVARCHAR(50), FechaRegistro DATETIME DEFAULT GETDATE() );

22. Luego el trigger : CREATE TRIGGER trg_RegistrarNuevaOrden ON SalesLT.SalesOrderHeader AFTER INSERT AS BEGIN INSERT INTO SalesLT.AuditoriaOrdenes ( SalesOrderID , CustomerID , OrderDate , Usuario) SELECT i.SalesOrderID , i.CustomerID , i.OrderDate , SYSTEM_USER FROM inserted i; END;

23. PRUEBA: INSERT INTO SalesLT.SalesOrderHeader ( SalesOrderNumber , OrderDate , CustomerID , ShipMethod , SubTotal , TaxAmt , Freight , TotalDue ) VALUES ('SO99999', GETDATE(), 1, 'CARGO TRANSPORT 5', 1000, 80, 50, 1130);

24. Trigger AFTER UPDATE Auditoría de cambios de precios Crear tabla de auditoría Primero, creamos una tabla que guardará los datos antiguos y nuevos del producto cuando cambie su precio.

25. CREATE TABLE SalesLT.AuditoriaPreciosProducto ( AuditID INT IDENTITY PRIMARY KEY, ProductID INT, NombreProducto NVARCHAR(100), PrecioAnterior MONEY, PrecioNuevo MONEY, Usuario NVARCHAR(50), FechaCambio DATETIME DEFAULT GETDATE() );

26. Este trigger se ejecutará después (AFTER) de una actualización en la tabla Product , solo si el precio ( ListPrice ) cambia.

27. CREATE TRIGGER trg_AuditoriaCambioPrecio ON SalesLT.Product AFTER UPDATE AS BEGIN SET NOCOUNT ON; INSERT INTO SalesLT.AuditoriaPreciosProducto ( ProductID , NombreProducto , PrecioAnterior , PrecioNuevo , Usuario ) SELECT d.ProductID , d.Name , d.ListPrice AS PrecioAnterior , i.ListPrice AS PrecioNuevo , SYSTEM_USER FROM deleted d INNER JOIN inserted i ON d.ProductID = i.ProductID WHERE d.ListPrice <> i.ListPrice ; END;

28. PRUEBA: -- Verifica el precio actual SELECT ProductID , Name , ListPrice FROM SalesLT.Product WHERE ProductID = 680; -- Actualiza el precio de un producto UPDATE SalesLT.Product SET ListPrice = 1200 WHERE ProductID = 680; -- Consulta el registro de auditoría SELECT * FROM SalesLT.AuditoriaPreciosProducto ;

29. Modificación de un desencadenador mediante ALTER TRIGGER

30. USE AdventureWorks2022; GO ALTER TRIGGER Sales.bonus_reminder ON Sales.SalesPersonQuotaHistory AFTER INSERT AS RAISERROR ('Notify Compensation', 16, 10); GO

31. VIEW

32. Una vista es una tabla virtual cuyo contenido está definido por una consulta. Al igual que una tabla, una vista consta de un conjunto de columnas y filas de datos con un nombre. Sin embargo, a menos que esté indizada, una vista no existe como conjunto de valores de datos almacenados en una base de datos.

33. Las filas y las columnas de datos proceden de tablas a las que se hace referencia en la consulta que define la vista y se producen de forma dinámica cuando se hace referencia a la vista.

34. Una vista actúa como filtro de las tablas subyacentes a las que se hace referencia en ella. La consulta que define la vista puede provenir de una o de varias tablas, o bien de otras vistas de la base de datos actual u otras bases de datos. Asimismo, es posible utilizar las consultas distribuidas para definir vistas que utilicen datos de orígenes heterogéneos

35. Tipos de vistas Indexed views Una vista indexada es una vista materializada. Esto significa que se ha calculado la definición de la vista y que los datos resultantes se han almacenado como una tabla. Se puede indizar una vista creando un índice clúster único en ella.

36. Las vistas indizadas pueden mejorar de forma considerable el rendimiento de algunos tipos de consultas. Las vistas indizadas funcionan mejor para consultas que agregan muchas filas. No son adecuadas para conjuntos de datos subyacentes que se actualizan frecuentemente.

37. Partitioned views Una vista con particiones combina datos horizontales con particiones de un conjunto de tablas miembro en uno o más servidores. Una vista con particiones hace que los datos aparezcan como si fuera una tabla. Una vista que combina tablas miembro en la misma instancia de SQL Server es una vista con particiones local.

38. System views Las vistas de sistema exponen metadatos de catálogo. Puede usar las vistas del sistema para devolver información acerca de la instancia de SQL Server o de los objetos definidos en la instancia. Por ejemplo, puede consultar la vista de catálogo sys.databases para devolver información sobre las bases de datos definidas por el usuario disponibles en la instancia.

39. Creación de vistas Para centrar, simplificar y personalizar la percepción de la base de datos para cada usuario. Como mecanismo de seguridad, que permite a los usuarios obtener acceso a los datos por medio de la vista, pero no les conceden el permiso de obtener acceso directo a las tablas base subyacentes de la vista. Para proporcionar una interfaz compatible con versiones anteriores para emular una tabla cuyo esquema ha cambiado.

40. USE AdventureWorks2022; GO CREATE VIEW HumanResources.EmployeeHireDate AS SELECT p.FirstName , p.LastName , e.HireDate FROM HumanResources.Employee AS e INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ; GO -- Query the view SELECT FirstName , LastName , HireDate FROM HumanResources.EmployeeHireDate ORDER BY LastName ; GO

41. Modificar vistas La modificación de una vista no afecta a ningún objeto dependiente, como procedimientos almacenados o desencadenadores, a menos que la definición de la vista cambie de forma que el objeto dependiente ya no sea válido.

42. Si una vista que está actualmente en uso se modifica mediante ALTER VIEW, el Motor de base de datos impone un bloqueo exclusivo de esquema sobre la vista. Cuando se concede el bloqueo, y no hay usuarios activos de la vista, el Motor de base de datos elimina todas las copias de la vista de la caché de procedimientos. Los planes existentes que hacen referencia a la vista permanecen en la caché, pero se vuelven a compilar cuando se llaman.

43. ALTER VIEW se puede aplicar a vistas indizadas; no obstante, quita incondicionalmente todos los índices de la vista.

44. USE AdventureWorks2022; GO -- Create a view CREATE VIEW HumanResources.EmployeeHireDate AS SELECT p.FirstName , p.LastName , e.HireDate FROM HumanResources.Employee AS e INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ; -- Modify the view by adding a WHERE clause to limit the rows returned ALTER VIEW HumanResources.EmployeeHireDate AS SELECT p.FirstName , p.LastName , e.HireDate FROM HumanResources.Employee AS e INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID WHERE HireDate < CONVERT(DATETIME, '20020101', 101); GO

45. Eliminar vistas Cuando se quita una vista, la definición y otra información de la vista se elimina del catálogo del sistema. También se eliminan todos los permisos de la vista. Las vistas de una tabla que se ha quitado mediante DROP TABLE se deben quitar explícitamente con DROP VIEW. DROP VIEW IF EXISTS HumanResources.EmployeeHireDate ; GO

46. Ejemplo de VIEW Esta vista muestra las órdenes con el nombre del cliente, la fecha, el total y el método de envío.

47. CREATE VIEW vw_OrdenesClientes AS SELECT C.CustomerID , P.FirstName + ' ' + P.LastName AS NombreCliente , H.SalesOrderID , H.OrderDate , H.ShipMethod , H.TotalDue FROM SalesLT.Customer AS C INNER JOIN SalesLT.SalesOrderHeader AS H ON C.CustomerID = H.CustomerID INNER JOIN SalesLT.CustomerAddress AS CA ON C.CustomerID = CA.CustomerID INNER JOIN SalesLT.Address AS A ON CA.AddressID = A.AddressID ;

48. PRUEBA: SELECT * FROM vw_OrdenesClientes WHERE ShipMethod = 'CARGO TRANSPORT 5';

49. Ejemplo de VIEW con JOIN entre productos y categorías vw_ProductosCategorias Lista todos los productos con su categoría y modelo.

50. CREATE VIEW vw_ProductosCategorias AS SELECT P.ProductID , P.Name AS NombreProducto , PC.Name AS Categoria , PM.Name AS Modelo, P.ListPrice AS Precio FROM SalesLT.Product AS P INNER JOIN SalesLT.ProductCategory AS PC ON P.ProductCategoryID = PC.ProductCategoryID INNER JOIN SalesLT.ProductModel AS PM ON P.ProductModelID = PM.ProductModelID ;

51. PRUEBA: SELECT * FROM vw_ProductosCategorias WHERE Categoria = 'Bikes';

52. Referencias https://learn.microsoft.com/es-es/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver17 https://microsoftlearning.github.io/dp-080-Transact-SQL/Instructions/Labs/03a-joins.html#use-a-cross-join https://learn.microsoft.com/es-es/sql/t-sql/language-elements/set-operators-union-transact-sql?view=sql-server-ver17