Implementación de desencadenadores

1. Implementación de desencadenadores

2. Introducción • Introducción a los desencadenadores • Definición de desencadenadores • Funcionamiento de los desencadenadores • Ejemplos de desencadenadores • Consideraciones acerca del rendimiento

3. Introducción a los desencadenadores • ¿Qué es un desencadenador? • Uso de los desencadenadores • Consideraciones acerca del uso de desencadenadores

4. ¿Qué es un desencadenador? • Asociación a una tabla • Invocación automática • Imposibilidad de llamada directa • Identificación con una transacción

5. Uso de los desencadenadores • Cambios en cascada en tablas relacionadas de una base de datos • Exigir una integridad de datos más compleja que una restricción CHECK • Definición de mensajes de error personalizados • Mantenimiento de datos no normalizados • Comparación del estado de los datos antes y después de su modificación

6. Consideraciones acerca del uso de desencadenadores • Los desencadenadores son reactivos, mientras que las restricciones son proactivas • Las restricciones se comprueban antes • Las tablas pueden tener varios desencadenadores para cualquier acción • Los propietarios de las tablas pueden designar el primer y último desencadenador que se debe activar • Debe tener permiso para ejecutar todas las instrucciones definidas en los desencadenadores • Los propietarios de tablas no pueden crear desencadenadores AFTER en vistas o en tablas temporales

7. Definición de desencadenadores • Creación de desencadenadores • Alteración y eliminación de desencadenadores

8. Creación de desencadenadores • Necesidad de los permisos adecuados • Imposibilidad de incluir determinadas instrucciones Use Northwind GO CREATE TRIGGER Empl_Delete ON Employees FOR DELETE AS IF (SELECT COUNT(*) FROM Deleted) > 1 BEGIN RAISERROR( 'You cannot delete more than one employee at a time.', 16, 1) ROLLBACK TRANSACTION END

9. Alteración y eliminación de desencadenadores • Alteración de un desencadenador • Cambios en la definición sin quitar el desencadenador • Deshabilitación o habilitación de un desencadenador • Eliminación de un desencadenador USE Northwind GO ALTER TRIGGER Empl_Delete ON Employees FOR DELETE AS IF (SELECT COUNT(*) FROM Deleted) > 6 BEGIN RAISERROR( 'You cannot delete more than six employees at a time.', 16, 1) ROLLBACK TRANSACTION END

10. Funcionamiento de los desencadenadores • Funcionamiento de un desencadenador INSERT • Funcionamiento de un desencadenador DELETE • Funcionamiento de un desencadenador UPDATE • Funcionamiento de un desencadenador INSTEAD OF • Funcionamiento de los desencadenadores anidados • Desencadenadores recursivos

11. Ejecución de acciones TRIGGER Trigger Code: USE Northwind CREATE TRIGGER OrdDet_Insert ON [Order Details] FOR INSERT AS UPDATE P SET UnitsInStock = (P.UnitsInStock – I.Quantity) FROM Products AS P INNER JOIN Inserted AS I ON P.ProductID = I.ProductID Instrucción INSERT en una tabla con un desencadenador INSERT Instrucción INSERT registrada Acción del desencadenador ejecutada 1 2 3 Order Details 10523 10523 2 2 19.00 19.00 5 5 0.2 0.2 Products OrderID ProductID UnitPrice Quantity Discount ProductID UnitsInStock … … 10522 10523 10524 10 41 7 31.00 9.65 30.00 79 24 0.20.15 0.0 1 2 3 4 15 106520 Instrucción INSERT registrada 2 15 inserted 10523 2 19.00 5 0.2 Funcionamiento de un desencadenador INSERT Instrucción INSERT en tabla con desencadenador INSERT INSERT [Order Details] VALUES (10525, 2, 19.00, 5, 0.2) Order Details OrderID ProductID UnitPrice Quantity Discount UPDATE P SET UnitsInStock = (P.UnitsInStock – I.Quantity) FROM Products AS P INNER JOIN Inserted AS I ON P.ProductID = I.ProductID 10522 10523 10524 10 41 7 31.00 9.65 30.00 79 24 0.20.15 0.0

12. Acción del desencadenador ejecutada Categories Instrucción DELETE en una tabla con un desencadenador DELETEInstrucción DELETE registradaAcción del desencadenador ejecutada CategoryID CategoryName Description Picture 1 Products 1 2 3 Beverages Condiments Confections Soft drinks, coffees… Sweet and savory … Desserts, candies, … 0x15…0x15… 0x15… ProductID Discontinued … … 1 2 3 4 0 000 2 2 1 USE Northwind CREATE TRIGGER Category_Delete ON Categories FOR DELETE AS UPDATE P SET Discontinued = 1 FROM Products AS P INNER JOIN deleted AS d ON P.CategoryID = d.CategoryID 3 4 Dairy Products Cheeses 0x15… Instrucción DELETE registrada Deleted 4 Dairy Products Cheeses 0x15… Funcionamiento de un desencadenador DELETE Instrucción DELETE en tabla con desencadenador DELETE Instrucción DELETE en tabla con desencadenador DELETE DELETE Categories WHERE CategoryID = 4 UPDATE P SET Discontinued = 1 FROM Products AS P INNER JOIN deleted AS d ON P.CategoryID = d.CategoryID

13. Ejecución de acciones TRIGGER USE Northwind GO CREATE TRIGGER Employee_Update ON Employees FOR UPDATE AS IF UPDATE (EmployeeID) BEGIN TRANSACTION RAISERROR ('Transaction cannot be processed.\ ***** Employee ID number cannot be modified.', 10, 1) ROLLBACK TRANSACTION Instrucción UPDATE en una tabla con un desencadenador UPDATE Instrucción UPDATE registrada como instrucciones INSERT y DELETE Acción del desencadenador ejecutada 1 Employees AS IF UPDATE (EmployeeID) BEGIN TRANSACTION RAISERROR ('Transaction cannot be processed.\ ***** Employee ID number cannot be modified.', 10, 1) ROLLBACK TRANSACTION EmployeeID LastName FirstName Title HireDate 2 1 2 3 4 Davolio Barr Leverling Peacock Nancy Andrew Janet Margaret Sales Rep. R Sales Rep. Sales Rep. ~~~ ~~~ ~~~ ~~~ 2 2 Fuller Fuller Andrew Andrew Vice Pres. Vice Pres. ~~~ ~~~ 3 Employees EmployeeID LastName FirstName Title HireDate 1 2 3 4 Davolio Barr Leverling Peacock Nancy Andrew Janet Margaret Sales Rep. R Sales Rep. Sales Rep. ~~~ ~~~ ~~~ ~~~ No se puede procesar la transacción. ***** No se puede modificar el número de miembro Funcionamiento de un desencadenador UPDATE Instrucción UPDATE para una tabla con un desencadenador UPDATE definido UPDATE Employees SET EmployeeID = 17 WHERE EmployeeID = 2 Instrucción UPDATE registrada como instrucciones INSERT yDELETE insertada 17 Fuller Andrew Vice Pres. ~~~ eliminada 2 Fuller Andrew Vice Pres. ~~~

14. El desencadenador INSTEAD OF puede estar enuna tabla o vista La acción que inicia el desencadenador no seproduce Permite actualizaciones en vistas que no se hanactualizado previamente 1 Customers CustomerID CompanyName Country Phone … 2 ALFKI ANATR ANTON Alfreds Fu… Ana Trujill… Antonio M… GermanyMexico Mexico 030-0074321 (5) 555-4729 (5) 555-3932 ~~~ ~~~ ~~~ ALFKI ALFKI Alfreds Fu… Alfreds Fu… Germany Germany 030-0074321 030-0074321 ~~~ ~~~ 3 CustomersMex CustomerID CompanyName Country Phone … CustomersGer ANATR ANTON CENTC Ana Trujill… Antonio M… Centro Co… Mexico Mexico Mexico (5) 555-4729 (5) 555-3932 (5) 555-3392 ~~~ ~~~ ~~~ CustomerID CompanyName Country Phone … ALFKI BLAUS DRACD Alfreds Fu… Blauer Se… Drachenb… GermanyGermany Germany 030-0074321 0621-08460 0241-039123 ~~~ ~~~ ~~~ Funcionamiento de un desencadenador INSTEAD OF Crea una vista que combina dos o más tablas CREATE VIEW Customers AS SELECT * FROM CustomersMexUNION SELECT * FROM CustomersGer Se ejecuta UPDATE en la vista El desencadenador INSTEAD OF dirige la actualización a la tabla base La inserción original a la vista Customers no se produce

15. Order_Details OrDe_Update OrderID ProductID UnitPrice Quantity Discount 10522 10523 10524 10 41 7 31.00 9.65 30.00 79 24 0.20.15 0.0 10525 2 19.00 5 0.2 La realización de un pedido provoca la ejecución del desencadenador OrDe_Update Se ejecuta la instrucción UPDATE en la tabla Products Products InStock_Update ProductID UnitsInStock … … 1 3 4 15 156520 2 15 Se ejecuta el desencadenadorInStock_Update Envía un mensaje UnitsInStock + UnitsOnOrder es < ReorderLevel para ProductID 2 Funcionamiento de los desencadenadores anidados 2 15

16. Desencadenadores recursivos • Activación recursiva de un desencadenador • Tipos de desencadenadores recursivos • Recursividad directa, que se da cuando un desencadenador se ejecuta y realiza una acción que lo activa de nuevo • Recursividad indirecta, que se da cuando un desencadenador se activa y realiza una acción que activa un desencadenador de otra tabla • Conveniencia del uso de los desencadenadores recursivos

17. Ejemplos de desencadenadores • Exigir la integridad de los datos • Exigir reglas de empresa

18. Exigir la integridad de los datos CREATE TRIGGER BackOrderList_Delete ON Products FOR UPDATEASIF (SELECT BO.ProductID FROM BackOrdersAS BO JOINInserted AS I ON BO.ProductID = I.Product_ID ) > 0BEGIN DELETE BO FROM BackOrders AS BOINNER JOIN Inserted AS ION BO.ProductID = I.ProductIDEND Products BackOrders ProductID UnitsInStock … … ProductID UnitsOnOrder … 1 3 4 15 106520 1 12 3 15 1065 Actualizada 2 15 El desencadenadorelimina la fila 2 15

19. Products Products Order Details ProductID ProductID UnitsInStock UnitsInStock … … … … OrderID ProductID UnitPrice Quantity Discount 1 2 3 4 1 3 4 15 106520 15 106520 10522 10523 10524 10525 10 2 41 7 31.00 19.00 9.65 30.00 79 24 0.20.15 0.0 2 0 9 Exigir reglas de empresa Los productos con pedidos pendientes no se pueden eliminar IF (Select Count (*) FROM [Order Details] INNER JOIN deleted ON [Order Details].ProductID = deleted.ProductID ) > 0 ROLLBACK TRANSACTION Se deshace la transacción El código deldesencadenador comprueba la tabla Order Details La instrucciónDELETE seejecutaen la tabla Product 'No puede procesarse la transacción' 'Este producto tiene historial de pedidos'

20. Consideraciones acerca del rendimiento • Los desencadenadores trabajan rápidamente porque las tablas insertadas y eliminadas están en la caché • El tiempo de ejecución está determinado por: • Número de tablas a las que se hace referencia • Número de filas afectadas • Las acciones contenidas en un desencadenador forman parte de una transacción