Uso de Triggers en SQL Server


Hola… una de las ventajas de estar registrado en diversas comunidades y grupos de usuarios es poder tener a la mano información sumamente valiosa y sobre todo el hecho de compartir conocimientos con otras personas… tal es el caso de la Comunidad MUG Huncayo donde uno de los colaboradores principales posteo el siguiente mensaje:

Algo resumido:

 
 

Triggers

En español se llaman o están traducidos por desencadenador son lo mismo que los Stored Procedures pero éstos se ejecutan desantendidamente y automáticamente cuando un usuario realiza una acción con la tabla de una base de datos que lleve asociado este trigger. Se pueden crear triggers para las sentencias de SQL Insert, Update y Delete.

Por ejemplo en la anterior tabla pr_usuararios podemos crear un trigger que cada vez que se inserte un nuevo registro envie un mail de aviso al webmaster del web (este ejemplo esta puesto al final de esta página).

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.

Los podemos crear desde el Entreprise Manager del SQL como muestran las figuras 1 y 2 o desde el Visual Interdev creando un proyecto de base de datos como muestra en la figura 3 y 4.

Desde el Trigger podremos obtener los datos de la fila que se ha modificado o añadido utilizando inserted o deleted:

Select * from deleted

Limitaciones de los triggers.

- Solo se pueden aplicar a una tabla especifica, 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 idoneo, 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 triggers :

ALTER DATABASE CREATE DATABASE
DISK INIT DISK RESIZE
DROP DATABASE LOAD DATABASE
LOAD LOG RECONFIGURE
RESTORE DATABASE RESTORE LOG

Ejemplo.

Como ejemplo crearemos un Trigger que avise al webmaster con un mail cuando un usuario se da de alta en nuestro web, para crear el trigger solo se han de seguir los pasos arriba indicados, como muestran las imágenes y el código es el siguiente, haciendo Copy & Paste funciona.

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

– Declaramos las variables del mensaje y del ID del nuevo usuario
Declare @Mensaje varchar(200)
Declare @ID numeric

– Obtenemos el id del usuario recien insertado
Select @ID = (Select IDUsuario From Inserted )
Select @Mensaje = ‘Nuevo Usuarios en el web : ‘ + Convert(varchar(10), @ID)

Exec master.dbo.xp_sendmail
@recipients = ‘webmaster@dominio.com’,
@subject = ‘Nuevo usuario’,
@message = @Mensaje

 

Fuente:
Publicado por JohnSuarez_NET

Otro Link referente al tema: http://technet.microsoft.com/es-es/library/ms203721.aspx

About these ads

27 comentarios el “Uso de Triggers en SQL Server

  1. Pingback: Los números de 2010 « Alex Jiménez

  2. Hola mucha gracias por el minitutorial, la verdad es bastante interesante este tema de los Triggers y Store Procedures:

    Pero tengo una pequeña pregunta que diferencia hay en hacer:
    Select @ID = (Select IDUsuario From Inserted ) como lo muestras en tu ejemplo y
    set @ID = (Select IDUsuario From Inserted )

    Gracias

  3. y podrian usar RequeredFieldValidator para los campos (Nombre y Correo)…..
    seria mas comodo para el usuario en vez de redireccionarlo a otra pagina; en caso de…

  4. Seria bueno que tomaran en cuenta el menú de la derecha:
    que algunos de los DIVs (si es lo que estan usando) o webparts; que no sobresalgan del cuerpo de la página, no se ven muy bien, manejen el tamaño y/o posicionamiento de dichos elementos…..

    Solo una sugerencia!

  5. Hola !!!
    Tengo una duda:
    SQLSERVER 2005
    Tengo dos tablas una llamada: Siembra y otra Cosecha, en la tabla cosecha esta una llave foranea de la tabla Siembra(idSiem). Intente hacer un trigger que al momento de eliminar un registro de la tabla Siembra , se eliminara antes en la tabla cosecha para que no saliera error de llave foranea, pero pues no me quedo, siguio saliendo el error de la llave foranea. Mi trigger se creo exitosamente pero no funciona, no se si para lo que quiero hacer se usen trigger o que se usa.

    El codigo que use es:

    CREATE TRIGGER borradoSiembraGDL
    ON siembraGDL
    FOR DELETE
    AS
    BEGIN
    DELETE cosechaGDL
    WHERE cosechaGDL.idCos = (SELECT cosechaGDL.idCos
    FROM cosechaGDL, deleted
    WHERE cosechaGDL.idSiem=deleted.idSiem)
    END

    Gracias por su ayuda :)

    • Debes relacionar la tablas a tarves de una clave foranea pero especificando que borre en cascada ante cualqueir borrado de registros. Es como cuando en access creas tablas y configuras para borrado automatico en casacada (esto se hace al crear la tabla relacionada con una preexistente). Igual hay que hacer eso con el SQL server, ya que un trigger no administrado solo se aplica para una sola tabla, pero con la solucion que te digo lineas arriba se hara automaticamente.

  6. Hola un favor.. quisiera crear un trigger, que automaticamente cuando estoy a punto de crear un nuevo registro, me cree un numero correlativo como identificador, en que momento tendria que hacerlo, puesto que al tratar de ponerlo en un trigger, este se ejecuta despues que he creado un registro, me gustaria que se cree antes,,,, asi mismo como hago para imposibilitar que un usuario modifique determinados campos de un determinado registro,,,,,,,,, trabajo con sql server 2005,,, Muchas gracias.. Deiby.

  7. Para Seda: seria mejor usar una clave foranea, a menos que desees que esos datos permanezcan intactos a pesar de ser modificados en la otra table. En ese caso, pues depende como lo quieras hacer, si es a la hora de insertar, eliminar o actualizar, pues seria con un trigger, si es en otro momento, pues busca la manera de guardarlos desde donde estes desarrollando, elejemplo del trigger podria ser

    create trigger trgLoQueSea
    on tabla
    for insert(delete o update)
    as
    insert into otraTabla values ((select algunCampo from inserted)) (si ya hay datos en la tabla que deseas agregar estos datos, seria un update otraTabla en vez de insert)
    go

    y pues asi…o asi lo haria yo jejeje. Ojala te haya ayudado

  8. Holas soy nuevo en esto de triggers y quisiera que me atudaran tengo el siguiente codigo y noda me sale error, help me
    CREATE TRIGGER triempleado
    BEFORE INSERT OR UPDATE OR DELETE ON empleado
    BEGIN
    IF DELETING THEN
    INSERT INTO historial_empleado (OPERACION, USUARIO, MODIFICADO)
    VALUES(‘ELIMINACION’, CURRENT_USER(), NOW());
    ELSE IF INSERTING THEN
    INSERT INTO historial_empleado (OPERACION, USUARIO,MODIFICADO, NEWNOM_EMPL,NEWCARGO_EMPL,NEWTELEF_EMPL,
    NEWSALRIO_EMPL, NEWDIREC_EMPL,GENERO_EMPL)
    VALUES(‘INSERCION’,CURRENT_USER(), NOW(),NEW.NOM_EMPL, NEW.CARGO_EMPL, NEW.SALARIO_EMPL,NEW.DIREC_EMPL,
    NEW.TELEF_EMPL,NEW.GENERO);
    ELSE
    INSERT INTO historial_empleado (OPERACION, USUARIO, MODIFICADO, OLDNOM_EMPL, NEWNOM_EMPL,OLDCARGO_EMPL,
    NEWCARGO_EMPL, OLDTELEF_EMPL, NEWTELEF_EMPL,OLDSALRIO_EMPL, NEWSALRIO_EMPL,OLDDIREC_EMPL, NEWDIREC_EMPL,
    GENERO_EMPL)
    VALUES(‘ACTUALIZACION’, CURRENT_USER(), NOW(),NEW.NOM_EMPL, OLD.NOM_EMPL, NEW.CARGO_EMPL,OLD.CARGO_EMPL,
    NEW.SALARIO_EMPL, OLD.SALRIO_EMPL, NEW.DIREC_EMPL, OLD.DIREC_EMPL, NEW.TELEF_EMPL, OLD.TELEF_EMPL,
    NEW.GENERO, OLD.GENERO);
    END IF;
    END;

    • Te faltaría poner begin end en cada uno de los else y después de los then, el then como tal en trigger no existe se pondría un begin end y luego el else begin end.
      es decir

      BEGIN
      IF DELETING
      begin
      INSERT INTO historial_empleado (OPERACION, USUARIO, MODIFICADO)
      VALUES(’ELIMINACION’, CURRENT_USER(), NOW());
      end
      else
      begin

      ……
      end

  9. Pingback: Uso de Triggers en SQL Server (Comentario de Jeyson) - Alex Jimenez

  10. Pingback: Uso de Triggers en SQL Server (Comentario de Jeyson) « Alexander Jiménez

  11. ejemplo clasico con las operaciones basicas…

    Create Table usuarios(
    cedula char (20) NOT NULL,
    descripcion char (20) null,
    primary key(cedula),
    )

    insert into usuarios values (‘1082908272′,’Administrador’)
    select * from usuarios
    insert into Auditoria values (‘1082908272′,’Sistemas’,’26/12/1989′)
    select * from Auditoria

    Create Table Auditoria(
    cedula char (20) NOT NULL,
    modulo char (20) null,
    Fecha_hora Datetime,
    )
    select * from Auditoria

    Create Trigger eliminar
    On usuarios
    Instead Of Delete
    As

    Insert Into Auditoria Select cedula,descripcion,GetDate() From deleted D
    Where EXISTS(Select cedula From usuarios Where cedula=D.cedula)

    Delete from usuarios where cedula=(select cedula From deleted D
    Where cedula=D.cedula)

    Delete usuarios where cedula = ‘000000’
    select * from auditoria
    select * from usuarios

    Insert Into usuarios Values (‘123456′,’asociado’)
    Insert Into usuarios Values (‘000000′,’invitado’)

    create TRIGGER actualizar
    on usuarios
    Instead Of update
    AS

    IF update (descripcion)
    BEGIN
    SET NOCOUNT ON;

    RAISERROR (‘Imposible actualizar los registros’,16, 1)

    ROLLBACK TRAN

    END
    GO

    select * from usuarios

    UPDATE usuarios
    SET descripcion=’empleado’
    WHERE descripcion = ‘asociado’

    CREATE TRIGGER SEGURIDAD
    ON DATABASE FOR Create_table,DROP_TABLE, ALTER_TABLE
    AS
    BEGIN
    RAISERROR (‘Imposible crear, borrar ni modificar tablas,Faltan permisos…’ , 16, 1)
    ROLLBACK TRANSACTION
    INSERT Auditoria VALUES (”,”,GetDate())
    END

    Disable Trigger SEGURIDAD On Database

    Enable Trigger actualizar On usuarios

  12. hola amigos, ueno quiero crear un backup con trigger que me permita enviar un mensaje a un correo,eso seria todo, gracias por su ayuda,saludos

  13. hola me podrian decir como hago para hacer las transsacciones con solo trigger ya cree la tabla transacciones con sus respectiva entidades o atributos necesito ayuda porfavor ghracias por su atencion

  14. HOLA, ALGUIEN ME PUEDE AYUDAR PARA DESIFRAR LA SINTAXIS DE UN
    INTERT INTO SUPPLIERSID(CONPANYNAME,CONTACTNAME, CONTACTTITLE,ADDRESS, CITY,REGION,COSTALCODE,COUNTRI,FONE) VALUE(…..) SI ALGUIEN ME PUEDIES AYUDAR CON ESTO ESTARIA AGRADECIDICIMO, GRACIAS.

    • INSERT INTO SE UTILIZA PARA AGREGAR DATOS A UNA TABLA DENTRO DE SQL SIN UTILIZAR EL ASISTENTE
      TE EXPLICO:
      INTERT INTO SUPPLIERSID(CONPANYNAME,CONTACTNAME, CONTACTTITLE,ADDRESS, CITY,REGION,COSTALCODE,COUNTRI,FONE) VALUE(…..)
      ESTA INSTRUCCION DICE ASI
      INSERTA DENTRO DE LA TABLA SUPPLIERSID, DENTRO DE LOS CAMPOS CONPANYNAME,CONTACTNAME, CONTACTTITLE,ADDRESS, CITY,REGION,COSTALCODE,COUNTRI,FONE
      LOS SIGUIETES VALORES (VALUES) E INMEDIATAMENTE TIENES QUE LISTAR LOS VALORES A AGREGAR ,RECUERDA SI SON DATOS DE TIPO ENTERO NO LLEVAN APOSTROFES SI SON FECHAS Y TEXTO SI LLEVAN APOSTROFE ‘México’ ‘/14/05/2011′ 1500 etc
      ESPERO TE SEA UTIL

  15. Hola Alexander.
    Muy interesante el blog, solo para aclarar lo siguiente:
    – Solo se pueden aplicar a una tabla especifica, es decir, un trigger no sirve para dos o más tablas.
    [Comentario]En Sql2005 se puede invocar a un trigger administrado por SQL/CLR en más de una tabla, es decir un trigger administrado, si puede servir para más de una tabla, por ejemplo yo uso un solo trigger de seguridad que se invoca en todas las tablas de mi solucion.

    Por otro lado el ejemplo no es el más idoneo para un trigger, debido que la transaccionalidad no deber enmarcarse sobre la regla del negocio, es decir si un usuario se da de alta alli deberia terminar la transacción y no cuando se envia el correo al administrador, recordar que las sentencias del trigger forma parte implicitamente de la transacción, para ello se debería utilizar el servicio de notificación que es un modelo más robusto y no forma parte de la transacción.
    Los trigger mayormente se usan para registros de tracking y seguridad.

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s