Triggers en Microsoft SQL Server 2005

Introducción:

Microsoft SQL Server 2005 proporciona dos mecanismos principales para exigir las reglas de negocios y la integridad de los datos: restricciones y triggers.

SQL Server incluye dos tipos generales de triggers: DML y DDL.

Los triggers DDL son nuevos en SQL Server 2005. Estos triggers se invocan cuando un evento de lenguaje de definición de datos (DDL) tiene lugar en el servidor o la base de datos.

Los triggers DML se invocan cuando un evento de lenguaje de manipulación de datos (DML) tiene lugar en la base de datos

Un trigger DML puede consultar otras tablas e incluir instrucciones Transact-SQL complejas. El trigger y la instrucción que lo activa se tratan como una sola transacción, que puede revertirse desde el trigger. Si se detecta un error grave (por ejemplo, no hay suficiente espacio en disco), se revierte automáticamente toda la transacción.

Los triggers DML tienen varias utilidades:

Ø  Pueden realizar cambios en cascada mediante tablas relacionadas de la base de datos; sin embargo, estos cambios pueden ejecutarse de manera más eficaz mediante restricciones de integridad referencial en cascada.

Ø  Pueden proteger contra operaciones INSERT, UPDATE y DELETE incorrectas o dañinas, y exigir otras restricciones que sean más complejas que las definidas con restricciones CHECK.
A diferencia de éstas, los triggers DML pueden hacer referencia a columnas de otras tablas. Por ejemplo, un trigger puede utilizar una instrucción SELECT de otra tabla para comparar con los datos insertados o actualizados y para realizar acciones adicionales, como modificar los datos o mostrar un mensaje de error definido por el usuario.

Ø  Pueden evaluar el estado de una tabla antes y después de realizar una modificación de datos y actuar en función de esa diferencia.

Ø  Varios triggers DML del mismo tipo (INSERT, UPDATE o DELETE) en una tabla permiten realizar distintas acciones en respuesta a una misma instrucción de modificación.

Los triggers DML se limitan al esquema de la tabla o vista en la que se crearon.
No es posible definir triggers DML en tablas temporales locales o globales.
El uso de WITH ENCRYPTION impide que el trigger se publique como parte de la réplica de SQL Server. WITH ENCRYPTION no se puede especificar para triggers CLR.
INSTEAD OF no se puede especificar para triggers DDL Los triggers INSTEAD OF no se pueden utilizar en vistas actualizables que usan WITH CHECK OPTION

Como máximo, se puede definir un trigger INSTEAD OF por cada instrucción INSERT, UPDATE o DELETE en cada tabla o vista. No obstante, en las vistas es posible definir otras vistas que tengan su propio trigger INSTEAD OF.

Para los triggers INSTEAD OF, no se permite la opción DELETE en tablas que tengan una relación de integridad referencial que especifica una acción ON DELETE en cascada. Igualmente, no se permite la opción UPDATE en tablas que tengan una relación de integridad referencial que especifica una acción ON UPDATE en cascada.

Una vez que CREATE TRIGGER ha terminado de ejecutarse, event_group también actúa como una macro al agregar los tipos de eventos que comprende a la vista de catálogo sys.trigger_events.

Las instrucciones de control de flujo no pueden dividirse en varios lotes, funciones definidas por el usuario o procedimientos almacenados.
Los triggers DML usan las tablas lógicas (conceptuales) deleted e inserted. Son de estructura similar a la tabla en que se define el trigger, es decir, la tabla en que se guarda la acción del usuario. Las tablas deleted e inserted
guardan los valores antiguos o nuevos de las filas que la acción del usuario puede cambiar

Los triggers DDL capturan información sobre el evento trigger mediante el uso de la función EVENTDATA . Devuelve información acerca de los eventos de base de datos o servidor. EVENTDATA se llama cuando se activa una notificación de eventos y el resultado se devuelve al Service Broker especificado. EVENTDATA también se puede utilizar dentro del cuerpo de un desencadenador DDL.

En el caso de un trigger CLR, especifica el método de enlace de un ensamblado con el trigger. El método no puede utilizar argumentos y debe devolver void.

Triggers DML

Los triggers DML se utilizan frecuentemente para imponer las reglas empresariales y la integridad de los datos. SQL Server proporciona integridad referencial declarativa (DRI) mediante las instrucciones ALTER TABLE y CREATE TABLE.

Sin embargo, DRI no proporciona integridad referencial entre bases de datos.

El primer y último trigger AFTER que se ejecuta en una tabla se puede especificar mediante sp_settriggerorder. Sólo se puede especificar el primer y último trigger AFTER para cada una de las operaciones INSERT, UPDATE y DELETE de una tabla. Si hay otros triggers AFTER en la misma tabla, se ejecutan aleatoriamente.

Si una instrucción ALTER TRIGGER modifica el primer o último trigger, se elimina el primer o último atributo establecido en el trigger modificado, y el valor del orden se debe restablecer mediante sp_settriggerorder.

 Limitaciones de los triggers

Un trigger se crea solamente en la base de datos actual; sin embargo, un trigger puede hacer referencia a objetos que están fuera de la base de datos actual.

Si es preciso que existan asignaciones de variable en un trigger, utilice una instrucción SET NOCOUNT al principio del mismo para impedir la devolución de cualquier conjunto de resultados.

 Un trigger DELETE no captura una instrucción TRUNCATE TABLE. Aunque una instrucción TRUNCATE TABLE es, de hecho, un trigger DELETE sin una cláusula WHERE, no se registra y, por tanto, no puede ejecutar un trigger.  Dado que el permiso de la instrucción TRUNCATE TABLE es del propietario de la tabla y no se puede transferir, sólo el propietario de la tabla debe preocuparse de invocar sin darse cuenta una instrucción TRUNCATE TABLE que no producirá la ejecución del trigger DELETE.

Triggers DDL

Los triggers DDL, al igual que los estándars, ejecutan procedimientos almacenados como respuesta a un evento. Pero a diferencia de los triggers estándar, no se ejecutan como respuesta a instrucciones UPDATE, INSERT o DELETE en una tabla o vista. En cambio, se ejecutan principalmente como respuesta a instrucciones de lenguaje de definición de datos (o DDL). Entre ellas se incluyen instrucciones CREATE, ALTER, DROP, GRANT, DENY, REVOKE y UPDATE STATISTICS. Algunos procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL también pueden activar triggers DDL.

 Por ejemplo, la instrucción CREATE TYPE y el procedimiento almacenado sp_addtype activarán un trigger DDL que se crea en un evento CREATE_TYPE

Los triggers DDL no se activan como respuesta a eventos que afectan a procedimientos almacenados y tablas temporales, ya sean locales o globales.

A diferencia de los triggers DML, los triggers DDL no tienen como ámbito los esquemas. Por tanto, OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY y OBJECTPROPERTYEX no se pueden utilizar para efectuar consultas en metadatos sobre triggers DDL. Utilice en su lugar las vistas de catálogo.

Los triggers DDL con ámbito en el servidor aparecen en el Object Explorer  de SQL Server Management Studio, en la carpeta Triggers. Dicha carpeta se encuentra en la carpeta Server Objects. Los triggers DDL con ámbito en la base de datos aparecen en la carpeta Database Triggers. Esta carpeta se encuentra en la carpeta Programmability de la base de datos correspondiente.

Notas generales sobre los triggers

Evite la devolución de conjuntos de resultados desde triggers en los nuevos trabajos de desarrollo y piense en modificar las aplicaciones que la usan actualmente. Para evitar que los triggers devuelvan conjuntos de resultados en SQL Server 2005, establezca la opción disallow results from triggers en 1.

 SQL Server permite que se creen varios triggers por cada evento DML o DDL. Por ejemplo, si se ejecuta CREATE TRIGGER FOR UPDATE para una tabla que ya tiene un trigger UPDATE, se creará un trigger de actualización adicional. En las versiones anteriores de SQL Server, sólo se permitía un trigger por cada evento de modificación (INSERT, UPDATE, DELETE) en cada tabla.

SQL Server permite también la invocación recursiva de triggers cuando el valor RECURSIVE_TRIGGERS está habilitado mediante ALTER DATABASE.

Deshabilitar RECURSIVE_TRIGGERS sólo evita las repeticiones directas. Para deshabilitar la repetición indirecta, establezca la opción nested triggers del servidor en 0 con sp_configure.

Si alguno de los triggers ejecuta una instrucción ROLLBACK TRANSACTION, no se ejecuta ningún trigger posterior, independientemente del nivel de anidamiento.

Los triggers pueden anidarse hasta un máximo de 32 niveles. Si un trigger cambia una tabla en la que hay otro trigger, el segundo se activa y puede, entonces, llamar a un tercero, y así sucesivamente. La configuración predeterminada permite triggers anidados.

 Los métodos invocados desde el código administrado no cuentan para este límite.

Resolución diferida de nombres

SQL Server permite triggers hagan referencia a tablas que no existen en el momento de la compilación. Esta capacidad se denomina resolución diferida de nombres.  Sin embargo se emitiría una advertencia en el momento de la creación sólo si el valor de nivel de compatibilidad se establece en 65.

Permisos:

Para crear un trigger DML, es necesario contar con permiso ALTER sobre la tabla o vista en la que se crea el trigger.

Para crear un trigger DDL con ámbito en el servidor (ON ALL SERVER) es necesario un permiso CONTROL SERVER sobre el servidor. Para crear un trigger DDL con ámbito en la base de datos (ON DATABASE) es necesario un permiso ALTER ANY DATABASE DDL TRIGGER en la base de datos actual.

Demos:

Utilizar un trigger DML con un mensaje de aviso

–El siguiente trigger DML imprime un mensaje en el cliente cuando alguien intenta agregar o cambiar datos en la tabla Customer.

CREATE TRIGGER reminder1

ON Sales.Customer

AFTER INSERT, UPDATE

AS RAISERROR (‘Notify Customer Relations’, 16, 10)

GO

Utilizar un trigger DML con un mensaje de correo electrónico de aviso

–Este ejemplo envía un mensaje de correo electrónico a una persona especificada (MaryM) cuando cambia la tabla Customer.

CREATE TRIGGER reminder2

ON Sales.Customer

AFTER INSERT, UPDATE, DELETE

AS

   EXEC msdb.dbo.sp_send_dbmail

        @profile_name = ‘AdventureWorks Administrator’,

        @recipients = ‘danw@Adventure-Works.com’,

        @body = ‘Don”t forget to print a report for the sales force.’,

        @subject = ‘Reminder’;

GO

Utilizar un trigger DML AFTER para exigir una regla de negocio entre las tablas PurchaseOrderHeader y Vendor

/*

Debido a que las restricciones CHECK sólo pueden hacer referencia a las columnas en que se han definido las restricciones de columna o de tabla, cualquier restricción de referencias cruzadas, en este caso, reglas de negocio, debe definirse como triggers.

 

En este ejemplo se crea un trigger DML. El trigger comprueba que la solvencia del proveedor es satisfactoria cuando se intenta insertar un nuevo pedido de compra en la tabla PurchaseOrderHeader. Para obtener la solvencia del proveedor, debe hacerse referencia a la tabla Vendor. Si la solvencia no es satisfactoria, se muestra un mensaje y no se ejecuta la inserción.

*/

CREATE TRIGGER LowCredit ON Purchasing.PurchaseOrderHeader

AFTER INSERT

AS

DECLARE @creditrating tinyint,

   @vendorid int

SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID

FROM Purchasing.PurchaseOrderHeader p INNER JOIN inserted i

     ON p.PurchaseOrderID = i.PurchaseOrderID

           JOIN Purchasing.Vendor v on v.VendorID = i.VendorID

IF @creditrating = 5

BEGIN

   RAISERROR (‘This vendor”s credit rating is too low to accept new

      purchase orders.’, 16, 1)

ROLLBACK TRANSACTION

END

Utilizar un trigger DDL con ámbito en la base de datos

— En el ejemplo siguiente se utiliza un trigger DDL para impedir que se quiten sinónimos en una base de datos

IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class = 0 AND name = ‘safety’)

     DROP TRIGGER safety

ON DATABASE

GO

CREATE TRIGGER safety

ON DATABASE

FOR DROP_SYNONYM

AS

   RAISERROR (‘You must disable Trigger "safety" to drop synonyms!’,10, 1)

   ROLLBACK

GO

Utilizar un trigger DDL con ámbito en el servidor

/*

En el ejemplo siguiente se utiliza un trigger DDL para imprimir un mensaje si se produce un evento CREATE DATABASE en la instancia actual del servidor, y se utiliza la función EVENTDATA para recuperar el texto de la instrucción Transact-SQL correspondiente.

*/

IF EXISTS (SELECT * FROM sys.server_triggers

    WHERE name = ‘ddl_trig_database’)

DROP TRIGGER ddl_trig_database

ON ALL SERVER

GO

CREATE TRIGGER ddl_trig_database

ON ALL SERVER

FOR CREATE_DATABASE

AS

    PRINT ‘Database Created.’

    SELECT EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,‘nvarchar(max)’)

GO

En todas las instalaciones de SQL Server 2005, el nivel de compatibilidad predeterminado es 90. Las bases de datos creadas en SQL Server 2005 se configuran en este nivel, excepto la base de datos model que tiene un nivel de compatibilidad inferior. Cuando se actualiza una base de datos de una versión anterior de SQL Server a SQL Server 2005, la base de datos mantiene su nivel de compatibilidad. Esto se aplica a las bases de datos del usuario y del sistema.

About justindeveloper

I am MCP (Microsoft Certified Professional). MCTS (Microsoft Certified Technology Specialist) and MCPD (Microsoft Certified Professional Developer), also I am SAP Business One Certified!! Desarrollando desde el IDE de Visual Studio NET 2003 hasta ahora con el Visual Studio NET 2010. Desde Microsoft SQL Server 2000 hasta ahora con el Microsoft SQL Server 2008 R2 y tambien con SharePoint, desde WSS 3.0 y MOSS 2007 y ahora familirizandome con el Sharepoint Foundation 2010 & Sharepoint Server 2010. The software development will follow being every time more wonderful!
This entry was posted in Development. Bookmark the permalink.

One Response to Triggers en Microsoft SQL Server 2005

  1. Carlos Jonathan Castro says:

    Como puedo dar permisos para ver la definicion del objeto? o como podria ver la estructura del mismo una vez creado?, espero puedas ayudarme, eh probado con sp_helptext y funciona en un ambiente de desarrollo sin muchos permisos, solo quisiera saber como otorgarle los permisos correspondientes a mi trigger
    Saludos!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s