Bases de Datos Relacionales

1. Bases de Datos Relacionales TRIGGERS Preparó: Ismael Castañeda Fuentes Fuentes: Manuales Sybase Manuales SQL Server Manuales Oracle

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. Trigger - Crear • Sintaxissimplificada: create triggertrigger_name ontable_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

9. Borrar Triggers • Sintaxis simplificada: droptriggertrigger_name • Ejemplo: drop trigger trg_i_sales

10. 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_helptrigger_name • Muestra información del trigger • sp_helptexttrigger_name • Muestra el código usado para crear el trigger • sp_renameold_trigger_name, new_trigger_name • Cambia el nombre del trigger

11. Trigger - ejemplo • Crear dos tablas: select * into myauthors from pubs2..authors create table myrecord ( mytime datetime, myrows 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

12. Triggers - ejemplo • Ejecutar un delete y ver la tabla myrecords: • delete from myauthors • where state = "CA"select * from myrecord • Ejecutar un delete que no afecta filas y ver la tabla myrecords : • delete from myauthors • where 1 = 2 • select * from myrecord • Borrar los objetos de base de datos creados: • drop table myauthors, myrecord

13. 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

14. Trigger - Borrados • A delete adds rows to the deleted table

15. 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

16. Trigger - Inserciones • insert añade filas en la tabla inserted

17. 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

18. Trigger - Actualizaciones Un update añade filas en ambas tablas

19. 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

20. 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

21. 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

22. Triggers y rollbacks • Tres tipos de rollbacks: • Deshacer el trigger • Deshacer el trigger y la sentencia que lo disparó • Deshacer toda la transacción

23. 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 Caso B begin tran ...(este caso insert ...ocaciona un print "in sp"error) ... commit tran print "sp done" Trigger begin tran s2 .... rollback tran s2 print “tr done” return

24. 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

25. Procedimiento almacenado Caso Cbegin tran ... insert ... print "in sp" ... commit tran print "sp done" Trigger ........rollback trigger print “tr done” return Deshacer un trigger

26. Procedimiento almacenado Case Dbegin tran ... insert ... print "in sp" ... commit tran print "sp done" Trigger begin tran ...rollback tran 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 Ebegin tran ... insert ... print "in sp" ... commit tran print "sp done" Trigger ........Rollback tran print “tr done” return

27. 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 asdeclare @num_rows intselect @num_rows = @@rowcountif @num_rows = 0 return -- 1) only one title at a time if @num_rows > 1 begin rollback trigger with raiserror 20011 "Can only update or add one title at a time." return end -- 2) occurs during the work week if (select datepart(dw, getdate())) in (1, 7) begin rollback trigger with raiserror 20012 "Can only update price during work day." return end -- 3) the price changes by 10% or less if (select new.price/old.price 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 endreturn go

28. Trigger - Prácticas recomendadas • Consideraciones al elaborar triggers: • @@rowcount • if update • triggers anidados • triggers recursivos

29. 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)]...

30. 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_idend return

31. 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 • @@nestlevelretorna el nivel de anidamiento

32. 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

33. Métodos para integridad de datos Dos métodos para implementar integridad de datos

34. Actualización de valores llave • Solamente en triggers es posible borrar o actualizar una llave primaria • Sólo en triggers es posible hacer cambios en cascada * Valores de llavesprimarias se puedenactualizar o borrarsi no estánreferencidos en llavesforáneas

35. 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_rowsrollback 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_salesdetforeign 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:

36. 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

37. 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