INSTITUTO TECNOLOGICO SUPERIOR DE COSAMALOAPAN
CARRERA: ING. SISTEMAS COMPUTACIONALES
CATEDRATICO: ROBERTO ZAMUDIO PORTILLA
MATERIA: TALLER DE BASE DE DATOS
ALUMNO: EMANUEL MARTINEZ IGLECIAS
ACTIVIDAD: DISPARADORES
FECHA: 28 DE NOVIEMBRE DEL 2012
Que es un Disparador en términos de base de datos Un trigger (o disparador) en una Base de datos , es un procedimiento que se ejecuta cuando se cumple una condición establecida al realizar una operación. Dependiendo de la base de datos, los triggers pueden ser de inserción (INSERT), actualización (UPDATE) o borrado (DELETE). Algunas bases de datos pueden ejecutar triggers al crear, borrar o editar usuarios, tablas, bases de datos u otros objetos. Elementos de la estructura de un disparado La función trigger debe ser creada antes que el trigger, y debe hacerse como una función sin argumentos, y códigos de retorno opacos. Existen dos tipos de disparadores que se clasifican según la cantidad de ejecuciones a realizar: Row Triggers (o Disparadores de fila): son aquellas que se ejecutaran n-veces si se llama n-veces desde la tabla asociada al trigger. Statement Triggers (o Disparadores de secuencia): son aquellos que sin importar la cantidad de veces que se cumpla con la condición, su ejecución es única. Pueden ser de sesión y almacenados; pero no son de fiar
La sintaxis para la creación de triggers es la s iguiente: CREATE TRIGGER
ON FOR EACH EXECUTE PROCEDURE (); El nombre del trigger se usará si se desea eliminar el trigger. Se usa como argumento del comando DROP TRIGGER. La palabra siguiente determina si la función debe ser llamada antes (BEFORE) o después (AFTER) del evento. El siguiente elemento del comando determina en que evento/s será llamada la función. Es posible especificar múltiples eventos utilizado el operador OR. El nombre de la relación (relation name) determinará la tabla afectada por el evento. La instrucción FOR EACH determina si el trigger se ejecutará para cada fila afectada o bien antes (o después) de que la secuencia se haya completado. El nombre del procedimiento (procedure name) es la función C llamada. Los argumentos son pasados a la función en la estructura CurrentTriggerData. El propósito de pasar los argumentos a la función es permitir a triggers diferentes con requisitos similares llamar a la misma función.
Cuál es la utilidad de un disparador Las funciones trigger retornan un área de tuplas (HeapTuple) al ejecutor. Esto es ignorado para trigger lanzados tras (AFTER) una operación INSERT, DELETE o UPDATE, pero permite lo siguiente a los triggers BEFORE: - retornar NULL e ignorar la operación para la tupla actual (y de este modo la tupla no será insertada/actualizada/borrada); - devolver un puntero a otra tupla (solo en eventos INSERT y UPDATE) que serán insertados (como la nueva versión de la tupla actualizada en caso de UPDATE) en lugar de la tupla original. Notar que no hay inicialización por parte del CREATE TRIGGER handler. Esto será cambiado en el futuro. Además, si más de un trigger es definido para el mismo evento en la misma relación, el orden de ejecución de los triggers es impredecible. Esto puede ser cambiado en el futuro. Si una función trigger ejecuta consultas SQL (utilizando SPI) entonces estas funciones pueden disparar nuevos triggers. Esto es conocido como triggers en cascada. No hay ninguna limitación explicita en cuanto al número de niveles de cascada. Si un trigger es lanzado por un INSERT e inserta una nueva tupla en la misma relación, el trigger será llamado de nuevo (por el nuevo INSERT). Actualmente, no se proporciona ningún mecanismo de sincronización (etc) para estos casos pero esto puede cambiar. Por el momento, existe una función llamada funny_dup17 () en los test de regresión que utiliza algunas técnicas para parar la recursividad (cascada) en si misma...
Sintaxis del uso de disparadores en: MySQL Un disparador se asocia con una tabla y se define para que se active al ocurrir una sentencia INSERT, DELETE, o UPDATE sobre dicha tabla. Puede también establecerse que se active antes o después de la sentencia en cuestión. Por ejemplo, se puede tener un disparador que se active antes de que un registro sea borrado, o después de que sea actualizado. Para crear o eliminar un disparador, se emplean las sentencias CREATE TRIGGER y DROP TRIGGER. La sintaxis de las mismas se describe en Sección 20.1, “Sintaxis de CREATE TRIGGER” y “Sintaxis de DROP TRIGGER”. Este es un ejemplo sencillo que asocia un disparador con una tabla para cuando reciba sentencias INSERT. Actúa como un acumulador que suma los valores insertados en una de las columnas de la tabla. La siguiente sentencia crea la tabla y un disparador asociado a ella: mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account -> FOR EACH ROW SET @sum = @sum + NEW.amount;
Para utilizar el disparador, se debe establecer el valor de la variable acumulador a cero, ejecutar una sentencia INSERT, y ver qué valor presenta luego la variable.
mysql> SET @sum = 0; mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00); mysql> SELECT @sum AS 'Total amount inserted'; +-----------------------+ | Total amount inserted | +-----------------------+ | 1852.48
|
+-----------------------+
En este caso, el valor de @sum luego de haber ejecutado la sentencia INSERT es 14.98 + 1937.50 100, o1852.48. Para eliminar el disparador, se emplea una sentencia DROP TRIGGER. El nombre del disparador debe incluir el nombre de la tabla: mysql> DROP TRIGGER account.ins_sum;
El disparador no puede referirse a tablas directamente por su nombre, incluyendo la misma tabla a la que está asociado. Sin embargo, se pueden emplear las palabras clave OLD y NEW. OLD se refiere a un registro existente que va a borrarse o que va a actualizarse antes de que esto ocurra. NEW se refiere a un registro nuevo que se insertará o a un registro modificado luego de que ocurre la modificación. El disparador no puede invocar procedimientos almacenados utilizando la sentencia CALL. (Esto significa, por ejemplo, que no se puede utilizar un procedimiento almacenado para eludir la prohibición de referirse a tablas por su nombre). El disparador no puede utilizar sentencias que inicien o finalicen una transacción, tal como START TRANSACTION,COMMIT, o ROLLBACK. MySQL gestiona los errores ocurridos durante la ejecución de disparadores de esta manera: Si lo que falla es un disparador BEFORE, no se ejecuta la operación en el correspondiente registro. Un disparador AFTER se ejecuta solamente si el disparador BEFORE (de existir) y la operación se ejecutaron exitosamente. Un error durante la ejecución de un disparador BEFORE o AFTER deriva en la falla de toda la sentencia que provocó la invocación del disparador. En tablas transaccionales, la falla de un disparador (y por lo tanto de toda la sentencia) debería causar la cancelación (rollback) de todos los cambios realizados por esa sentencia. En tablas no transaccionales, cualquier cambio realizado antes del error no se ve afectado.
SQL Server La estructura de un trigger es: Create Trigger pr_usuarios_Trigger1 On dbo.pr_usuarios For /* Insert, Update, Delete */ As Los triggers pueden incluir cualquier número y clase de instrucción de Transact-SQL. Desde el Trigger podremos obtener los datos de la fila que se ha modificado o añadido utilizando inserted o deleted: Select * from deleted
SQL Server proporciona los siguientes tipos de triggers: Trigger DML, se ejecutan cuando un usuario intenta modificar datos mediante un evento de lenguaje de manipulación de datos (DML). Los eventos DML son instrucciones INSERT, UPDATE o DELETE de una tabla o vista. Trigger DDL, se ejecutan en respuesta a una variedad de eventos de lenguaje de definición de datos (DDL). Estos eventos corresponden principalmente a instrucciones CREATE, ALTER y DROP de Transact-SQL, y a determinados procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL. Trigger DML. Los trigger DML se ejecutan cuando un usuario intenta modificar datos mediante un evento de lenguaje de manipulación de datos (DML). Los eventos DML son instrucciones INSERT, UPDATE o DELETE de una tabla o vista. La sintaxis general de un trigger es la siguiente.
CREATE TRIGGER ON AFTER AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here END
Trigger DDL Los trigger DDL se ejecutan en respuesta a una variedad de eventos de lenguaje de definición de datos (DDL). Estos eventos corresponden principalmente a instrucciones CREATE, ALTER y DROP de Transact-SQL, y a determinados procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL. La sintaxis general de un trigger es la siguiente.
CREATE TRIGGER ON DATABASE FOR AS BEGIN ... END
Limitaciones de los triggers.
Solo se pueden aplicar a una tabla específica, es decir, un trigger no sirve para dos o más tablas. El trigger se crea en la base de datos que de trabajo pero desde un trigger puedes hacer referencia a otras bases de datos. Un Trigger devuelve resultados al programa que lo desencadena de la misma forma que un Stored Procedure aunque no es lo mas idóneo, para impedir que una instrucción de asignación devuelva un resultado se puede utilizar la sentencia SET NOCOUNT al principio del Trigger. Las siguientes instrucciones no se pueden utilizar en los t riggers: ALTER DATABASE CREATE DATABASE DISK INIT DISK RESIZE DROP DATABASE LOAD DATABASE LOAD LOG RECONFIGURE RESTORE DATABASE RESTORE LOG
Oracle Para diseñar un disparador hay que cumplir dos requisitos: Especificar las condiciones en las que se va a ejecutar el disparador. Esto se descompone en un evento que causa la comprobación del disparador y una condición que se debe cumplir para ejecutar el disparador. Especificar las acciones que se van a realizar cuando se ejecute el disparador. Los triggers PL/SQL constituyen una potente herramienta para mantener la integridad de la base de datos, ya que pueden llevar a cabo cualquier acción que sea necesaria para el mantenimiento de dicha integridad. Los triggers PL/SQL pueden llamar a otros procedimientos y disparar otros triggers, pero no admiten parámetros y no pueden ser invocados desde otros procedimientos PL/SQL. La sintaxis de un disparador Oracle es:
CREATE [OR REPLACE] TRIGGER nombre {BEFORE | AFTER | INSTEAD OF} // Temporalidad del Evento {INSERT | DELETE | UPDATE [OF ]} ON
[FOR EACH ROW | STATEMENT] //Granularidad [WHEN condición]
[DECLARE//Declaración de variables …] BEGIN Cuerpo del trigger [EXCEPTION …] END;
Lógica
Código
Eliminar un disparador: DROP TRIGGER nombre_disparador; Activar/ Desactivar disparadores: Existen dos opciones. ALTER TRIGGER nombre_disparador {DISABLE | ENABLE}; ALTER TABLE nombre_tabla {ENABLE | DISABLE} ALL TRIGGERS; Temporalidad del Evento: AFTER / BEFORE
Evento
BEFORE: Ejecutan la acción asociada antes de que la sentencia sea ejecutada: – Decidir si la acción debe realizarse o no – Utilizar valores alternativos para la sentencia
CREATE TRIGGER NombreTrigger BEFORE Insert ON NombreTabla …. AFTER: Ejecutan la acción asociada después de que se haya ejecutado la sentencia
CREATE TRIGGER NombreTrigger AFTER Insert ON NombreTabla …. INSTEAD OF: Desde Oracle 8 se proporciona los disparadores de sustitución, con ciertas restricciones INSTEAD OF es una cláusula válida solo para vistas; no se puede especificar un disparador INSTEAD OF en una tabla. Si una vista tiene un disparador INSTEAD OF, cualquier vista creada sobre ésta debe tener a su vez un disparador INSTEAD OF. Cuando definimos disparadores INSTEAD OF para columnas LOB, podemos leer tanto el seudoregistro: OLD como el seudo-registro: NEW, pe ro no se puede modificar sus valores.
Ejemplo MySQL mysql> delimiter // mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account -> FOR EACH ROW -> BEGIN -> IF NEW.amount < 0 THEN -> SET NEW.amount = 0; -> ELSEIF NEW.amount > 100 THEN -> SET NEW.amount = 100; -> END IF; -> END;// mysql> delimiter ; create trigger UNO after insert on producto for each row begin if now.precio<0 then insert into invalidos values (now.nombre,new precios); ena if end//
create trigger eliminar after delete on clients for each row insert into id_cliente(nombre, seccion, usuarios id_cliente) values (old.nombre,old.seccion,corrent_user(),now(),old.id_cliente);
Ejemplo de SQL server El funcionamiento del trigger es muy sencillo, declaramos dos variables, una para el mensaje que se enviará en el mail y otra para obtener el ID del registro recién insertado y luego este ID lo concatenamos al mensaje para enviárselo al webmaster. Alter Trigger Trigger_Aviso_al_Webmaster On dbo.pr_usuarios For Insert As Declare @Mensaje varchar(200) Declare @ID numeric Select @ID = (Select IDUsuario From Inserted ) Select @Mensaje = ‘Nuevo Usuarios en el we b : ‘ + Convert(varchar(10), @ID) Exec master.dbo.xp_sendmail @recipients = ‘[email protected]’, @subject = ‘Nuevo usuario’, @message = @Mensaje create trigger dbo.movimientosdelete on dbo.movimientos for delete as begin set nocount on update dbo.articulos set stock = stock – t.parcial from dbo.articulos a inner join ( select articulo_id, sum(case when tipo=’i’ then cantidad else -cantidad end) as parcial from deleted group by articulo_id) t on a.id = t.articulo_id end CREATE TRIGGER StatusChangeDateTrigger ON expedientes AFTER UPDATE AS IF UPDATE(state) BEGIN
UPDATE expedientes SET stateChangedDate=GetDate() WHERE code=(SELECT code FROM insert ed); INSERT INTO expStatusHistory (code, state) (SELECT code, state FROM deleted WHERE code=del eted.code); END;
Ejemplo de Oracle CREATE OR REPLACE TRIGGER DIS_PAGO BEFORE UPDATE OF f_pago ON Pago FOR EACH ROW WHEN (new.f_pago > old.f_venc) BEGIN raise_application_error(-20000, 'Pago ' || TO_CHAR(:old.nPago) || ' del prestamo ' ||TO_CHAR(:old.nPrestamo) || ' vencida. Por favor, dirigirse a la gere ncia.'); END; Escribir un disparador de base de datos que haga fallar cualquier o peración de modificación del apellido o del número de un empleado, o que suponga una subida de sueldo superior al 10%. CREATE OR REPLACE TRIGGER fallo_modif BEFORE UPDATE OF apellido, emp_no, salario ON emple FOR EACH ROW BEGIN IF UPDATING('emp_no') OR UPDATING('apellido') OR (UPDATING ('salario') AND :new.salario>:old.salario*1.1) THEN RAISE_APPLICATION_ERROR (-20001,'Err. Modificacion no permitida'); END IF; END;
Construir un disparador que permita realizar operaciones de actualización en la tabla depart a partir de la vista dptos, de forma similar al ejemplo del trigger t_ges_emplead. Se contemplarán las siguientes operaciones: - Insertar departamento. - Borrar departamento. - Modificar la localidad de un departamento. CREATE OR REPLACE TRIGGER ges_depart INSTEAD OF DELETE OR INSERT OR UPDATE ON DEPARTAM FOR EACH ROW BEGIN IF DELETING THEN
DELETE FROM depart WHERE dept_no = :old.dept_no; ELSIF INSERTING THEN INSERT INTO depart VALUES(:new.dept_no, :new.dnombre, :new.loc); ELSIF UPDATING('loc') THEN UPDATE depart SET loc = :new.loc WHERE dept_no = :old.dept_no; ELSE RAISE_APPLICATION_ERROR (-20001,'Error en la actualización'); END IF; END;
Conclusion En términos de base de datos, los trigger o bien conocido como disparadores son una de las herramientas que ofrecen los gestores de datos aun que cada uno tiene una forma de aplicarlos. Los disparadores o trigger son utilizados antes de crear, borrar o editar usuarios, tablas, bases de datos u otros objetos. Los trigger solamente pueden agregar (insert), modificar (update) o eliminar (delete) datos. En los trigger o disparadores solo hay dos tiempos el antes de?, y el después de?, After y Before. Los trigger por lo regular son más usados en tablas que esto ya existen en la base de datos, solo pueden ser usado uno de ellos por cada acción, insert, update, delete. Para eliminar los trigger simplemente se debe de usa el nombre de este y el comando: drop trigger. Su función en invisible ante la compresión de los usuarios ya que estos se disparan antes o después de ejecutar alguna acción. Son una herramienta que ayudan a corregir errores o a detenerlos sin tener que interactuar directamente con el objeto.
Fuentes De Information http://es.wikipedia.org/wiki/Trigger_(base_de_datos) http://es.wikipedia.org/wiki/Trigger_(base_de_datos) http://dev.mysql.com/doc/refman/5.0/es/using-triggers.html http://alexjimenez.wordpress.com/2007/08/13/uso-de-triggers-en-sql-server/ http://www.devjoker.com/contenidos/catss/278/Triggers-en-Transact-SQL.aspx http://www.paginasprodigy.com.mx/evaristopacheco/taller/disparadores.html
