Embed Size (px)
Citation preview
Implementación de
desencadenadores
Introducción
Introducción a los desencadenadores
Definición de desencadenadores
Funcionamiento de los desencadenadores
Ejemplos de desencadenadores
Consideraciones acerca del rendimiento
Introducción a los desencadenadores
¿Qué es un desencadenador?
Uso de los desencadenadores
Consideraciones acerca del uso de desencadenadores
¿Qué es un desencadenador?
Asociación a una tabla
Invocación automática
Imposibilidad de llamada directa
Identificación con una transacción
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
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
Definición de desencadenadores
Creación de desencadenadores
Alteración y eliminación de desencadenadores
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
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
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
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
10522
10523
10524
ProductID
10
41
7
UnitPrice
31.00
9.65
30.00
Quantity
7
9
24
Discount
0.2
0.15
0.0
519.002 0.210523
Instrucción INSERT registrada
inserted
10523 2 19.00 5 0.2
Ejecución de acciones TRIGGER
Order Details
OrderID
10522
10523
10524
ProductID
10
41
7
UnitPrice
31.00
9.65
30.00
Quantity
7
9
24
Discount
0.2
0.15
0.0
519.002 0.210523
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
UPDATE P SET
UnitsInStock = (P.UnitsInStock – I.Quantity)
FROM Products AS P INNER JOIN Inserted AS I
ON P.ProductID = I.ProductID
Products
ProductID UnitsInStock … …
1
2
3
4
15
10
65
20
2 15
Instrucción INSERT en una tabla con un
desencadenador INSERT
Instrucción INSERT registrada
Acción del desencadenador ejecutada
1
2
3
Funcionamiento de un desencadenador DELETE
Instrucción DELETE en tabla con desencadenador DELETEInstrucción DELETE en tabla con desencadenador DELETE
Deleted
4 Dairy Products Cheeses 0x15…
Instrucción DELETE registrada
Categories
CategoryID
1
2
3
CategoryName
Beverages
Condiments
Confections
Description
Soft drinks, coffees…
Sweet and savory …
Desserts, candies, …
Picture
0x15…
0x15…
0x15…
0x15…CheesesDairy Products4
DELETE Categories
WHERE
CategoryID = 4
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
Products
ProductID Discontinued … …
1
2
3
4
0
0
0
0
Acción del desencadenador ejecutada
2 1
UPDATE P SET Discontinued = 1
FROM Products AS P INNER JOIN deleted AS d
ON P.CategoryID = d.CategoryID
Instrucción DELETE en una tabla con un
desencadenador DELETE
Instrucción DELETE registrada
Acción del desencadenador ejecutada
1
2
3
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 y
DELETE
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.
~~~
~~~
~~~
~~~
2 Fuller Andrew Vice Pres. ~~~
insertada
17 Fuller Andrew Vice Pres. ~~~
eliminada
2 Fuller Andrew Vice Pres. ~~~
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
AS
IF UPDATE (EmployeeID)
BEGIN TRANSACTION
RAISERROR ('Transaction cannot be processed.\
***** Employee ID number cannot be modified.', 10, 1)
ROLLBACK TRANSACTION
No se puede procesar la transacción.***** No se puede modificar el número de miembro
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.
~~~
~~~
~~~
~~~
2 Fuller Andrew Vice Pres. ~~~
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
2
3
Funcionamiento de un desencadenador INSTEAD OF
Crea una vista que combina dos o más tablas
CREATE VIEW
Customers AS
SELECT *
FROM CustomersMex
UNION
SELECT *
FROM CustomersGer
CustomersMex
CustomerID CompanyName Country Phone …
ANATR
ANTON
CENTC
Ana Trujill…
Antonio M…
Centro Co…
Mexico
Mexico
Mexico
(5) 555-4729
(5) 555-3932
(5) 555-3392
~~~
~~~
~~~
CustomersGer
CustomerID CompanyName Country Phone …
ALFKI
BLAUS
DRACD
Alfreds Fu…
Blauer Se…
Drachenb…
Germany
Germany
Germany
030-0074321
0621-08460
0241-039123
~~~
~~~
~~~
El desencadenador
INSTEAD OF dirige
la actualización a la
tabla base
Customers
CustomerID CompanyName Country Phone …
ALFKI
ANATR
ANTON
Alfreds Fu…
Ana Trujill…
Antonio M…
Germany
Mexico
Mexico
030-0074321
(5) 555-4729
(5) 555-3932
~~~
~~~
~~~La inserción
original a la vista
Customers no se
produce
Se ejecuta UPDATE
en la vista
ALFKI Alfreds Fu… Germany 030-0074321 ~~~
ALFKI Alfreds Fu… Germany 030-0074321 ~~~
El desencadenador INSTEAD OF puede estar en
una tabla o vista
La acción que inicia el desencadenador no se
produce
Permite actualizaciones en vistas que no se han
actualizado previamente
1
2
3
Funcionamiento de los desencadenadores anidados
2 15
UnitsInStock + UnitsOnOrder
es < ReorderLevel para ProductID 2
OrDe_Update
La realización de un pedido provoca la ejecución del desencadenador OrDe_Update
Se ejecuta la instrucción UPDATE en la tabla Products
InStock_UpdateProducts
ProductID UnitsInStock … …
1
3
4
15
15
65
20
Se ejecuta el desencadenadorInStock_Update
Envía un mensaje
Order_Details
OrderID
10522
10523
10524
ProductID
10
41
7
UnitPrice
31.00
9.65
30.00
Quantity
7
9
24
Discount
0.2
0.15
0.0
10525 19.002 0.25
2 15
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
Ejemplos de desencadenadores
Exigir la integridad de los datos
Exigir reglas de empresa
Exigir la integridad de los datos
CREATE TRIGGER BackOrderList_Delete
ON Products FOR UPDATE
AS
IF (SELECT BO.ProductID FROM BackOrders AS BO JOIN
Inserted AS I ON BO.ProductID = I.Product_ID
) > 0
BEGIN
DELETE BO FROM BackOrders AS BO
INNER JOIN Inserted AS I
ON BO.ProductID = I.ProductID
END
Products
ProductID UnitsInStock … …
1
3
4
15
10
65
20
2 15 Actualizada
BackOrders
ProductID UnitsOnOrder …
1
12
3
15
10
65
2 15 El desencadenador
elimina la fila
Products
ProductID UnitsInStock … …
1
2
3
4
15
10
65
20
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
La instrucción DELETE seejecuta en la tabla Product
El código del desencadenadorcomprueba la tabla Order Details
Order Details
OrderID
10522
10523
10524
10525
ProductID
10
2
41
7
UnitPrice
31.00
19.00
9.65
30.00
Quantity
7
9
24
Discount
0.2
0.15
0.0
9
'No puede procesarse la transacción'
'Este producto tiene historial de pedidos'
Se deshacela transacción
Products
ProductID UnitsInStock … …
1
3
4
15
10
65
20
2 0
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
