Transcript SQL_Triggers_v3

Bases de Datos
Relacionales
TRIGGERS
Preparó: Ismael Castañeda Fuentes
Fuentes: Manuales Sybase
Manuales SQL Server
Manuales Oracle
Trigger
Un trigger es un procedimiento almacenado asociado con una tabla, el cual
se ejecuta automáticamente cuando se modifica un dato de esa tabla
¿Se puede
llamar
explícitamente?
¿Se puede
ejecutar
automáticamente?
¿Puede usar
parámetros?
Procedimiento
definido por el
usuario
Sí
No
Sí
Trigger
No
Sí
No
Trigger - Aplicaciones Típicas
• Hacer modificarciones en cascada sobre tablas relacionadas
• Deshacer cambios que violan la integridad de los datos
• Forzar restricciones que son muy complejas para reglas y restricciones
• Mantener datos duplicados
• Mantener columnas con datos derivados
• Hacer ajustes de registros
Trigger - Definición
• Un trigger se define asociado con una tabla para una o más sentencias de
manipulación de datos
– Un trigger se puede definir para insert, update, o delete o cualquier combinación de
ellos
Trigger - Activación
• Cuando se modifica un dato en una tabla que tiene declarado un trigger para esa
sentencia, el trigger se “dispara”
– El trigger se dispara una vez, independientemente del número de filas afectadas
– El trigger se dispara aunque no hayan filas afectadas
Triggers and transacciones
• Un trigger es parte de la transacción que causa el disparo
• El trigger puede deshacer:
– Así mismo solamente
– Así mismo y la sentencia que causa el disparo
– La transacción total
Trigger - Reglas
• Los triggers pueden:
– Declarar variables locales
– Invocar procedimientos almacenados
• Los triggers no pueden:
–
–
–
–
Llamarse directamente
Usar parámetros
Definirse sobre tablas temporales o vistas
Crear objetos permanentes de base de datos
• Las operaciones con registro mínimo (como select into) no disparan los
triggers
Trigger - Crear
• Sintaxis simplificada:
create trigger trigger_name
on table_name
for {insert | update | delete} [, {insert | update | delete} ...]
as
sql_statements
• Ejemplo:
create trigger trg_i_sales
on sales
for insert
as
if datename (dd,getdate()) = "Sun"
begin
raiserror 40070, "Sales cannot be
processed on Sunday."
rollback trigger
end
Borrar Triggers
• Sintaxis simplificada:
drop trigger trigger_name
• Ejemplo:
drop trigger trg_i_sales
Procedimientos del sistema para Triggers
• sp_depends {table_name | trigger_name}
– Cuando se da el nombre de tabla, lista todos los objetos (incluyendo triggers) de la
misma base de dtos
– Cuando se da el nombre de trigger, lista todas las tablas referencias
• sp_help trigger_name
– Muestra información del trigger
• sp_helptext trigger_name
– Muestra el código usado para crear el trigger
• sp_rename old_trigger_name, new_trigger_name
– Cambia el nombre del trigger
Trigger - ejemplo
• Crear dos tablas:
select * into myauthors
from pubs2..authors
create table myrecord (
mytime
myrows
)
datetime,
int
• Crear un trigger que guarde la fecha y número de filas afectadas por cada delete:
create trigger trg_d_myauthors
on myauthors
for delete
as
insert into myrecord
values (getdate(), @@rowcount)
return
Triggers - ejemplo
•
Ejecutar un delete y ver la tabla myrecords:
•
Ejecutar un delete que no afecta filas y ver la tabla myrecords :
•
Borrar los objetos de base de datos creados:
delete from myauthors
where state = "CA"
select * from myrecord
delete from myauthors
where 1 = 2
select * from myrecord
drop table myauthors, myrecord
Trigger - Tablas inserted y deleted
• inserted y deleted son dos tablas que se crean automáticamente cada vez que se
dispara un trigger
– inserted almacena cualquier fila que se vaya a añadir a la tabla
– deleted almacena cualquier fila que se vaya a borrar de la tabla
Trigger - Borrados
• A delete adds rows to the deleted table
Trigger - Uso de la tabla deleted
create trigger trg_d_publishers
on publishers
for delete
as
-- Exit trigger if no rows were modified.
if @@rowcount = 0
return
-- For deleted publishers, delete
-- corresponding titles
delete titles
from titles t, deleted d
where t.pub_id = d.pub_id
-- Appropriate actions would take place here
-- since a deleted publisher has far-reaching
-- effects throughout the database.
return
Trigger - Inserciones
• insert añade filas en la tabla inserted
Trigger - Uso de la tabla inserted
-- Make sure all au_ids match
if (select count(*) from authors a, inserted i
where a.au_id=i.au_id ) <> @num_rows
begin
raiserror 31114 "Attempt to insert
invalid au_id into titleauthor."
rollback transaction
return
end
return
Go
-- Insert and update trigger on titleauthor
create trigger trg_iu_titleauthor
on titleauthor
for insert, update as
-- Find out how many rows were modified
declare @num_rows int
select @num_rows=@@rowcount
if @num_rows=0
return
-- Make sure all title_ids match
if (select count(*) from titles t, inserted i
where t.title_id=i.title_id) <> @num_rows
begin
raiserror 31113 "Attempt to insert
invalid title_id into titleauthor."
rollback transaction
return
end
Trigger - Actualizaciones
Un update añade filas en ambas tablas
Trigger - Tablas inserted y deleted
-- Insert, update, and delete trigger on salesdetail
create trigger trig_iud_salesdetail
on salesdetail
for insert, update, delete
as
-- Exit trigger if no rows were modified.
if @@rowcount = 0
return
-- If a new quantity has been inserted or updated for a
-- given title_id, add the value to titles.total_sales. The
-- isnull function is used because titles.total_sales might
-- be NULL.
update titles
set total_sales = isnull(total_sales, 0) +
(select sum(qty)
from inserted
where titles.title_id = inserted.title_id)
where title_id in (select title_id from inserted)
-- If an old quantity has been updated or deleted for a
-- given title_id, subtract the value from
-- titles.total_sales. The isnull function is used because
-- titles.total_sales might be NULL.
update titles
set total_sales = isnull(total_sales, 0) (select sum(qty)
from deleted
where titles.title_id = deleted.title_id)
where title_id in (select title_id from deleted)
return
Trigger - Reglas para tablas inserted y deleted
• Ambas tablas tienen las mismas columnas que la tabla asociada al trigger
• El trigger puede consultar datos de las dos tablas
– Otros procesos no pueden consultar datos de las dos tablas
• El trigger no puede modificar datos en las dos tablas
• Cada anidamiento de triggers tiene sus propias tablas inserted y deleted
– Si un trigger modifica datos de su tabla asociada, esos cambios no se reflejan en
las tablas inserted and deleted de ese trigger
Trigger - Tablas inserted and deleted
• Crear una tabla:
select * into myauthors from pubs2..authors
• Crear un trigger que use la función suser_name( ) para listar la(s) fila(s) que
se borraron y el nombre del usuario:
create trigger trg_d_myauthors
on myauthors
for delete
as
select suser_name(), "deleted these rows:"
select * from deleted
return
• Ejecutar un delete que afecte más de una fila:
delete from myauthors
where state = "CA“
• Ejecutar un delete que no afecte filas:
delete from myauthors
where 1 = 2
• Borrar los objetos de base de datos:
drop table myauthors
Triggers y rollbacks
• Tres tipos de rollbacks:
– Deshacer el trigger
– Deshacer el trigger y la sentencia que lo disparó
– Deshacer toda la transacción
Deshacer un trigger
• Para deshacer un trigger, declarar un punto de grabación y luego hacer el rollback
– Un rollback sin punto de grabación deshace toda la transacción
Procedimiento almacenado
Caso A
begin tran
...
insert ...
print "in sp"
...
commit tran
print "sp done"
Trigger
save tran s1
....
rollback tran s1
print “tr done”
return
Procedimiento almacenado
Trigger
Caso B
begin tran s2
....
rollback tran s2
print “tr done”
return
(este caso
ocaciona un
error)
begin tran
...
insert ...
print "in sp"
...
commit tran
print "sp done"
Deshacer un trigger
• rollback trigger deshace el trigger y la sentencia que lo disparó
• Sintaxis:
rollback trigger [with raiserror error_number [error_statement] ]
• Ejemplo:
create trigger trg_i_publishers
on publishers
for insert
as
if @@rowcount > 1
begin
rollback trigger with raiserror 40031
"You cannot insert more than one
publisher at a time."
return
end
Deshacer un trigger
Procedimiento almacenado
Caso C
begin tran
...
insert ...
print "in sp"
...
commit tran
print "sp done"
Trigger
....
....
rollback trigger
print “tr done”
return
Deshacer una transacción
Para deshacer toda la transacción donde está inmerso el trigger, ejecutar un rollback
sin un punto de grabación
Procedimiento almacenado
Case D
begin tran
...
insert ...
print "in sp"
...
commit tran
print "sp done"
Procedimiento almacenado
Case E
begin tran
...
insert ...
print "in sp"
...
commit tran
print "sp done"
Trigger
begin tran
...
rollback tran
print "tr done”
return
Trigger
....
....
Rollback tran
print “tr done”
return
Triggers y rollbacks
-----
Book price can be updated if:
1) only one title is updated at a time
2) the update occurs during the work week
3) the price changes by 10% or less
-- 4) only books with sales > 0 are updated
create trigger trg_u_titles on titles for update
as
declare @num_rows int
select @num_rows = @@rowcount
if @num_rows = 0
return
-- 1) only one title at a time
if @num_rows > 1
begin
rollback trigger
"Can only update
return
end
-- 2) occurs during the work week
if (select datepart(dw, getdate())) in (1, 7)
begin
rollback trigger
"Can only update
return
end
-- 3) the price changes by 10% or less
if (select new.price/old.price
with raiserror 20011
or add one title at a time."
with raiserror 20012
price during work day."
from inserted new, deleted old)
not between 1.10 and 0.90
begin
rollback trigger with raiserror 20013
"Can only change price by 10 percent."
return
end
-- 4) only books with sales > 0
if (select isnull(total_sales, 0) from inserted) <= 0
begin
rollback trigger with raiserror 20014
"Can only change price of book with sales."
return
end
return
go
Trigger - Prácticas recomendadas
• Consideraciones al elaborar triggers:
–
–
–
–
@@rowcount
if update
triggers anidados
triggers recursivos
Trigger - if update
• if update es una condición que le permite a un trigger chequear si ha habido un
cambio en una determinada columna
• Sólo se puede usar en triggers
• Usualmente se usa para chequear si el valor de una llave primaria ha cambiado
• Sintaxis simplificada:
if update (column_name) [ {and | or} update (column_name)]...
Trigger - if update
-- Update trigger on publishers table
create trigger trg_u_publishers
on publishers
for update
as
-- Find out how many rows were modified
declare @num_rows int
select @num_rows=@@rowcount
if @num_rows=0
return
if update ( pub_id )
-- Was primary key updated?
begin
-- Multiple updated rows not allowed
if @num_rows > 1
begin
raiserror 31113 "Updates to primary
keys of multiple rows is not permitted."
rollback transaction
return
end
-- Cascade update to titles table
update titles
set t.pub_id = new.pub_id
from titles t, inserted new, deleted old
where t.pub_id = old.pub_id
end
return
Triggers anidados
• Un trigger anidado es un trigger que se dispara en respuesta a una modificación
hecha en un trigger
• Nivel máximo de anidamiento: 16
– Tanto los procedimientos almacenados como los triggers cuentan en la determinación del
nivel máximo
– @@nestlevel retorna el nivel de anidamiento
Triggers recursivos
• Un trigger recursivo es aquel que se dispara cuando modifica su propia tabla
• Por default, un trigger que modifica su propia tabla no causa un disparo recursivo
del trigger
Métodos para integridad de datos
Dos métodos para implementar integridad de datos
Integridad a
nivel de
Dominio
Restricciones Checks
Objetos de
base de
datos
Reglas
Integridad a
Integridad
nivel de Entidad Referencial
Primary key,
restricciones
unique
Referencias
Indices
Triggers
Actualización de valores llave
Acción deseada
Restricciones
Triggers
Insertarar valor de llave primaria
Permitido
Permitido
Insertar valor de llave foránea
Permitido
Permitido
Actualizar valor de llave primaria
No permitido*
Permitido
Actualizar valor de llave foránea
Permitido
Permitido
Borrar valor de llave primaria
No permitido*
Permitido
Borrar valor de llave foránea
Permitido
Permitido
* Valores de llaves primarias se pueden actualizar o borrar si no están referencidos en llaves foráneas
• Solamente en triggers es posible borrar o actualizar una llave primaria
• Sólo en triggers es posible hacer cambios en cascada
Ejemplo
Ejemplo para mantener la integridad referencial de la llave foránea cuando se inserta o
actualiza la tabla salesdetail:
create trigger trg_iu_sales
on salesdetail
for insert, update as
declare @num_rows int
select @num_rows = @@rowcount
if @num_rows = 0
return
if (select count (*)
from titles t, inserted i
where t.title_id = i.title_id)
<> @num_rows
rollback transaction
if (select count (*) from
sales s, inserted i
where s.stor_id = i.stor_id
and s.ord_num = i.ord_num)
<> @num_rows
rollback transaction
return
create table salesdetail
(stor_id char(4)
NOT NULL,
ord_num varchar(20)
NOT NULL,
title_id typ_ch_tid
NOT NULL
references titles(title_id),
qty
smallint NOT NULL,
discount float
NOT NULL,
constraint ref_salesdet
foreign key (stor_id, ord_num)
references sales (stor_id,
ord_num) )
Ejemplo
Ejemplo para mantener la integridad referencial de la llave foránea cuando se inserta o
actualiza la tabla salesdetail:
create trigger trg_du_titles
on titles
for delete, update as
-- prevent delete or update of
-- title_id if there are foreign key
-- values that reference them
..
return
create trigger trg_du_sales
on sales
for delete, update as
-- prevent delete or update of
-- stor_id/ord_num if there are
-- foreign key values that
-- reference them
...
return
Restricciones versus triggers
• Ventajas de las restricciones:
– Las restricciones (y reglas) son más rápidas que los triggers
– Las restricciones no requieren codificación adicional
– Es mejor para chequear datos antes de ingresarlos a la base de datos
• Ventajas de los triggers:
– Muy flexible
• Los triggers pueden hacer cualquier cosa que se pueda codificar
– Mejor para las reglas complejas del negocio que no se pueden expresar como
restricciones referenciales tales como actualizaciones o borrados en cascada