Trigger (base de datos)De Wikipedia, la enciclopedia libre(Redirigido desde Disparador (base de datos))Saltar a navegación, búsqueda

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 de inserción (INSERT), actualización (UPDATE) o borrado (DELETE).

Contenido[ocultar]

1 Usos 2 Componentes principales 3 Tipos 4 Efectos y características 5 Ejemplo 6 Disparadores en MySQL 7 Disparadores en PostgresQL

8 Enlaces externos

[editar] Usos

Son usados para mejorar la administración de la Base de datos, sin necesidad de contar con que el usuario ejecute la sentencia de SQL.

Además, pueden generar valores de columnas, previene errores de datos, sincroniza tablas, modifica valores de una vista, etc.

Permite implementar programas basados en paradigma lógico (sistemas expertos, deducción).

[editar] Componentes principales

La estructura básica de un trigger es:

Llamada de activación: es la sentencia que permite "disparar" el código a ejecutar.

Restricción: es la condición necesaria para realizar el código. Esta restricción puede ser de tipo condicional o de tipo nulidad.

Acción a ejecutar: es la secuencia de instrucciones a ejecutar una vez que se han cumplido las condiciones iniciales.

[editar] Tipos

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 áquellos 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[cita requerida].

[editar] Efectos y características No aceptan parámetros o argumentos (pero podrían almacenar los datos

afectados en tablas temporales) No pueden ejecutar las operaciones COMMIT o ROLLBACK por que estas son

parte de la sentencia SQL del disparador (únicamente a través de transacciones autónomas)

Pueden causar errores de mutaciones en las tablas, si se han escrito de manera deficiente.

[editar] Ejemplo

Un sencillo ejemplo sería crear un Trigger para insertar un pedido de algún producto cuando la cantidad de éste, en nuestro almacén, sea inferior a un valor dado.

BEFORE UPDATE ON tabla_almacenFOR ALL records IF :NEW.producto < 100 THEN INSERT INTO tabla_pedidos(producto) VALUES ('1000'); END IF;SELECT DBO.POLVE.TESTEND

[editar] Disparadores en MySQL

Los disparadores son soportados en MySQL a partir de la versión 5.0.2. Algunos de los soportes existentes son los disparadores para las sentencias INSERT, UPDATE y DELETE

El estándar SQL:2003 requiere que los disparadores den a los programadores acceso a las variables de un registro utilizando una sintaxis como REFERENCING NEW AS n. Por ejemplo, si un disparador está monitoreando los cambios en la columna salario, podría escribirse un disparador como:

CREATE TRIGGER ver_salario BEFORE UPDATE ON empleados REFERENCING NEW ROW AS n, OLD ROW AS o FOR EACH ROW IF n.salario <> o.salario THEN END IF;

Como en MySQL las sentencias se ejecutan luego de escribir el signo punto y coma (;), cabe destacar que para crear un disparador en MySQL, antes se escribe la sentencia DELIMITER seguida de un carácter tal como |, la cual asigna la función del punto y coma (;) a otro carácter permitiendo que el disparador sea escrito usando los punto y

comas sin que se ejecute mientras se escribe; después de escrito el disparador se escribe nuevamente la sentencia DELIMITER ; para asignar al punto y coma su función habitual.

[editar] Disparadores en PostgresQL

Desde 1997 PostgresQL soporta el uso de disparadores, estos pueden anexarse a las tablas pero no a las vistas; aunque a las vistas se les pueden crear reglas.

Al igual que en MySQL los disparadores de PostgresQL se pueden activar luego de sentencias INSERT, UPDATE o DELETE

Cuando hay varios disparadores, se activan en orden alfabético.

Además de permitir el uso de funciones en el lenguaje nativo de PostgresQL, PL/PgSQL, los disparadores también permiten invocar funciones escritas en otros lenguajes como PL/Perl.

En Postgres un disparador ejecuta una función la cual contiene el código de lo que se requiere, esto difiere del método expuesto anteriormente para MySQL que escribe el código a ejecutarse dentro del mismo disparador.

El siguiente es un ejemplo de disparador creado con su respectiva función:

CREATE OR REPLACE FUNCTION actualizar() RETURNS TRIGGER AS $ejemplo$ BEGIN NEW.nombre := NEW.nombres || ' ' || NEW.apellidos ; RETURN NEW; END;$ejemplo$ LANGUAGE plpgsql; CREATE TRIGGER ejemploBEFORE INSERT OR UPDATE ON tabla FOR EACH ROW EXECUTE PROCEDURE actualizar();

CREATE TRIGGERSQL Server 2000

  Topic last updated -- June 2007

Creates a trigger, which is a special kind of stored procedure that executes automatically when a user attempts the specified data-modification statement on the specified table. Microsoft® SQL Server™ allows the creation of multiple triggers for any given INSERT, UPDATE, or DELETE statement.

Syntax

CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] {

    { {FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }         [ WITH APPEND ]         [ NOT FOR REPLICATION ]         AS         [ { IF UPDATE ( column )             [ { AND | OR } UPDATE ( column ) ]                 [ ...n ]         | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )                { comparison_operator } column_bitmask [ ...n ]         } ]         sql_statement [...n ]     } }

Arguments

trigger_name

Is the name of the trigger. A trigger name must conform to the rules for identifiers and must be unique within the database. Specifying the trigger owner name is optional.

Table | view

Is the table or view on which the trigger is executed and is sometimes called the trigger table or trigger view. Specifying the owner name of the table or view is optional. A view can be referenced only by an INSTEAD OF trigger.

WITH ENCRYPTION

Indicates that SQL Server will convert the original text of the CREATE TRIGGER statement to an obfuscated format. Note that obfuscated triggers can be reverse engineered because SQL Server must de-obfuscate triggers for execution. In SQL Server 2000, the obfuscated text is visible in the syscomments system table and may be susceptible to de-obfuscation attempts.

Using WITH ENCRYPTION prevents the trigger from being published as part of SQL Server replication.

AFTER

Specifies that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger executes.

AFTER is the default, if FOR is the only keyword specified.

AFTER triggers cannot be defined on views.

INSTEAD OF

Specifies that the trigger is executed instead of the triggering SQL statement, thus overriding the actions of the triggering statements.

At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, it is possible to define views on views where each view has its own INSTEAD OF trigger.

INSTEAD OF triggers are not allowed on updateable views WITH CHECK OPTION. SQL Server will raise an error if an INSTEAD OF trigger is added to an updateable view WITH CHECK OPTION specified. The user must remove that option using ALTER VIEW before defining the INSTEAD OF trigger.

{ [DELETE] [,] [INSERT] [,] [UPDATE] }

Are keywords that specify which data modification statements, when attempted against this tableor view, activate the trigger. At least one option must be specified. Any combination of these in any order is allowed in the trigger definition. If more than one option is specified, separate the options with commas.

For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action ON DELETE. Similarly, the UPDATE option is not allowed on tables that have a referential relationship specifying a cascade action ON UPDATE.

WITH APPEND

Specifies that an additional trigger of an existing type should be added. Use of this optional clause is needed only when the compatibility level is 65 or lower. If the compatibility level is 70 or higher, the WITH APPEND clause is not needed to add an additional trigger of an existing type (this is the default behavior of CREATE TRIGGER with the compatibility level setting of 70 or higher.) For more information, see sp_dbcmptlevel.

WITH APPEND cannot be used with INSTEAD OF triggers or if AFTER trigger is explicitly stated. WITH APPEND can be used only when FOR is specified (without INSTEAD OF or AFTER) for backward compatibility reasons. WITH APPEND and FOR (which is interpreted as AFTER) will not be supported in future releases.

NOT FOR REPLICATION

Indicates that the trigger should not be executed when a replication process modifies the table involved in the trigger.

AS

Are the actions the trigger is to perform.

sql_statement

Is the trigger condition(s) and action(s). Trigger conditions specify additional criteria that determine whether the attempted DELETE, INSERT, or UPDATE statements cause the trigger action(s) to be carried out.

The trigger actions specified in the Transact-SQL statements go into effect when the DELETE, INSERT, or UPDATE operation is attempted.

Triggers can include any number and kind of Transact-SQL statements. A trigger is designed to check or change data based on a data modification statement; it should not return data to the user. The Transact-SQL statements in a trigger often include control-of-flow language. A few special tables are used in CREATE TRIGGER statements:

deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use:

SELECT * FROM deleted

In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext, or image column references in the inserted and deleted tables if the compatibility level is equal to 70. The text, ntext, and image values in the inserted and deleted tables cannot be accessed. To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table. When the compatibility level is 65 or lower, null values are returned for inserted or deletedtext, ntext, or image columns that allow null values; zero-length strings are returned if the columns are not nullable.

If the compatibility level is 80 or higher, SQL Server allows the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.

n

Is a placeholder indicating that multiple Transact-SQL statements can be included in the trigger. For the IF UPDATE (column) statement, multiple columns can be included by repeating the UPDATE (column) clause.

IF UPDATE (column)

Tests for an INSERT or UPDATE action to a specified column and is not used with DELETE operations. More than one column can be specified. Because the table name is specified in the ON clause, do not include the table name before the column name in an IF UPDATE clause. To test for an INSERT or UPDATE action for more than one column, specify a separate UPDATE(column) clause following the first one. IF UPDATE will return the TRUE value in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.

Note  The IF UPDATE (column) clause functions identically to an IF, IF...ELSE or WHILE statement and can use the BEGIN...END block. For more information, see Control-of-Flow Language.

UPDATE(column) can be used anywhere inside the body of the trigger.

column

Is the name of the column to test for either an INSERT or UPDATE action. This column can be of any data type supported by SQL Server. However, computed columns cannot be used in this context. For more information, see Data Types.

IF (COLUMNS_UPDATED())

Tests, in an INSERT or UPDATE trigger only, whether the mentioned column or columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit pattern that indicates which columns in the table were inserted or updated.

The COLUMNS_UPDATED function returns the bits in order from left to right, with the least significant bit being the leftmost. The leftmost bit represents the first column in the table; the next bit to the right represents the second column, and so on. COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than 8 columns, with the least significant byte being the leftmost. COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.

COLUMNS_UPDATED can be used anywhere inside the body of the trigger.

bitwise_operator

Is the bitwise operator to use in the comparison.

updated_bitmask

Is the integer bitmask of those columns actually updated or inserted. For example, table t1 contains columns C1, C2, C3, C4, and C5. To check whether columns C2, C3, and C4 are all updated (with table t1 having an UPDATE trigger), specify a value of 14. To check whether only column C2 is updated, specify a value of 2.

comparison_operator

Is the comparison operator. Use the equal sign (=) to check whether all columns specified in updated_bitmask are actually updated. Use the greater than symbol (>) to check whether any or some of the columns specified in updated_bitmask are updated.

column_bitmask

Is the integer bitmask of those columns to check whether they are updated or inserted.

Remarks

Triggers are often used for enforcing business rules and data integrity. SQL Server provides declarative referential integrity (DRI) through the table creation statements (ALTER TABLE and CREATE TABLE); however, DRI does not provide cross-

database referential integrity. To enforce referential integrity (rules about the relationships between the primary and foreign keys of tables), use primary and foreign key constraints (the PRIMARY KEY and FOREIGN KEY keywords of ALTER TABLE and CREATE TABLE). If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and prior to the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed (fired).

The first and last AFTER triggers to be executed on a table may be specified by using sp_settriggerorder. Only one first and one last AFTER trigger for each of the INSERT, UPDATE, and DELETE operations may be specified on a table; if there are other AFTER triggers on the same table, they are executed randomly.

If an ALTER TRIGGER statement changes a first or last trigger, the first or last attribute set on the modified trigger is dropped, and the order value must be reset with sp_settriggerorder.

An AFTER trigger is executed only after the triggering SQL statement, including all referential cascade actions and constraint checks associated with the object updated or deleted, has executed successfully. The AFTER trigger sees the effects of the triggering statement as well as all referential cascade UPDATE and DELETE actions caused by the triggering statement.

If an INSTEAD OF trigger defined on a table executes a statement against the table that would usually fire the INSTEAD OF trigger again, the trigger is not called recursively. Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions. For example, if a trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the table.

If an INSTEAD OF trigger defined on a view executes a statement against the view that would usually fire the INSTEAD OF trigger again, it is not called recursively. Instead, the statement is resolved as modifications against the base tables underlying the view. In this case, the view definition must meet all of the restrictions for an updatable view. For a definition of updatable views, see Modifying Data Through a View. For example, if a trigger is defined as an INSTEAD OF UPDATE trigger for a view, and the trigger executes an UPDATE statement referencing the same view, the UPDATE statement executed by the INSTEAD OF trigger does not call the trigger again. The UPDATE executed by the trigger is processed against the view as if the view did not have an INSTEAD OF trigger. The columns changed by the UPDATE must be resolved to a single base table. Each modification to an underlying base table starts the chain of applying constraints and firing AFTER triggers defined for the table.

Trigger Limitations

CREATE TRIGGER must be the first statement in the batch and can apply to only one table.

A trigger is created only in the current database; however, a trigger can reference objects outside the current database.

If the trigger owner name is specified (to qualify the trigger), qualify the table name in the same way.

The same trigger action can be defined for more than one user action (for example, INSERT and UPDATE) in the same CREATE TRIGGER statement.

INSTEAD OF DELETE/UPDATE triggers cannot be defined on a table that has a foreign key with a cascade on DELETE/UPDATE action defined.

Any SET statement can be specified inside a trigger. The SET option chosen remains in effect during the execution of the trigger and then reverts to its former setting.

When a trigger fires, results are returned to the calling application, just as with stored procedures. To eliminate having results returned to an application due to a trigger firing, do not include either SELECT statements that return results, or statements that perform variable assignment in a trigger. A trigger that includes either SELECT statements that return results to the user or statements that perform variable assignment requires special handling; these returned results would have to be written into every application in which modifications to the trigger table are allowed. If variable assignment must occur in a trigger, use a SET NOCOUNT statement at the beginning of the trigger to eliminate the return of any result sets.

A TRUNCATE TABLE statement is not caught by a DELETE trigger. Although a TRUNCATE TABLE statement is, in effect, a DELETE without a WHERE clause (it removes all rows), it is not logged and thus cannot execute a trigger. Because permission for the TRUNCATE TABLE statement defaults to the table owner and is not transferable, only the table owner should be concerned about inadvertently circumventing a DELETE trigger with a TRUNCATE TABLE statement.

The WRITETEXT statement, whether logged or unlogged, does not activate a trigger.

These Transact-SQL statements are not allowed in a trigger:

ALTER DATABASE CREATE DATABASE DISK INIT

DISK RESIZE DROP DATABASE LOAD DATABASE

LOAD LOG RECONFIGURE RESTORE DATABASE

RESTORE LOG    

Note  Because SQL Server does not support user-defined triggers on system tables, it is recommended that no user-defined triggers be created on system tables.

Multiple Triggers

SQL Server allows multiple triggers to be created for each data modification event (DELETE, INSERT, or UPDATE). For example, if CREATE TRIGGER FOR

UPDATE is executed for a table that already has an UPDATE trigger, then an additional update trigger is created. In earlier versions, only one trigger for each data modification event (INSERT, UPDATE, DELETE) was allowed for each table.

Note  The default behavior for CREATE TRIGGER (with the compatibility level of 70) is to add additional triggers to existing triggers, if the trigger names differ. If trigger names are the same, SQL Server returns an error message. However, if the compatibility level is equal to or less than 65, any new triggers created with the CREATE TRIGGER statement replace any existing triggers of the same type, even if the trigger names are different. For more information, see sp_dbcmptlevel.

Recursive Triggers

SQL Server also allows recursive invocation of triggers when the recursive triggers setting is enabled in sp_dboption.

Recursive triggers allow two types of recursion to occur:

Indirect recursion

Direct recursion

With indirect recursion, an application updates table T1, which fires trigger TR1, updating table T2. In this scenario, trigger T2 then fires and updates table T1.

With direct recursion, the application updates table T1, which fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.

This example uses both indirect and direct trigger recursion. Assume that two update triggers, TR1 and TR2, are defined on table T1. Trigger TR1 updates table T1 recursively. An UPDATE statement executes each TR1 and TR2 one time. In addition, the execution of TR1 triggers the execution of TR1 (recursively) and TR2. The inserted and deletedtables for a given trigger contain rows corresponding only to the UPDATE statement that invoked the trigger.

Note  The above behavior occurs only if the recursive triggers setting of sp_dboption is enabled. There is no defined order in which multiple triggers defined for a given event are executed. Each trigger should be self-contained.

Disabling the recursive triggers setting only prevents direct recursions. To disable indirect recursion as well, set the nested triggers server option to 0 using sp_configure.

If any of the triggers do a ROLLBACK TRANSACTION, regardless of the nesting level, no further triggers are executed.

Nested Triggers

Triggers can be nested to a maximum of 32 levels. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on. If any trigger in the chain sets off an infinite loop, the nesting level is

exceeded and the trigger is canceled. To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). The default configuration allows nested triggers. If nested triggers is off, recursive triggers is also disabled, regardless of the recursive triggers setting of sp_dboption.

Deferred Name Resolution

SQL Server allows Transact-SQL stored procedures, triggers, and batches to refer to tables that do not exist at compile time. This ability is called deferred name resolution. However, if the Transact-SQL stored procedure, trigger, or batch refers to a table defined in the stored procedure or trigger, a warning is issued at creation time only if the compatibility level setting (set by executing sp_dbcmptlevel) is equal to 65. A warning is issued at compile time if a batch is used. An error message is returned at run time if the table referenced does not exist. For more information, see Deferred Name Resolution and Compilation.

Permissions

CREATE TRIGGER permissions default to the table owner on which the trigger is defined, the sysadmin fixed server role, and members of the db_owner and db_ddladmin fixed database roles, and are not transferable.

To retrieve data from a table or view, a user must have SELECT statement permission on the table or view. To update the content of a table or view, a user must have INSERT, DELETE, and UPDATE statement permissions on the table or view.

If an INSTEAD OF trigger exists on a view, the user must have INSERT, DELETE, and UPDATE privileges on that view to issue INSERT, DELETE, and UPDATE statements against the view, regardless of whether the execution actually performs such an operation on the view.

Examples

A. Use a trigger with a reminder message

This example trigger prints a message to the client when anyone tries to add or change data in the titles table.

Note  Message 50009 is a user-defined message in sysmessages. For more information about creating user-defined messages, see sp_addmessage.

USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'reminder' AND type = 'TR') DROP TRIGGER reminderGOCREATE TRIGGER reminderON titlesFOR INSERT, UPDATE AS RAISERROR (50009, 16, 10)GO

B. Use a trigger with a reminder e-mail message

This example sends an e-mail message to a specified person (MaryM) when the titles table changes.

USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'reminder' AND type = 'TR') DROP TRIGGER reminderGOCREATE TRIGGER reminderON titlesFOR INSERT, UPDATE, DELETE AS EXEC master..xp_sendmail 'MaryM', 'Don''t forget to print a report for the distributors.'GO

C. Use a trigger business rule between the employee and jobs tables

Because CHECK constraints can reference only the columns on which the column- or table-level constraint is defined, any cross-table constraints (in this case, business rules) must be defined as triggers.

This example creates a trigger that, when an employee job level is inserted or updated, checks that the specified employee job level (job_lvls), on which salaries are based, is within the range defined for the job. To get the appropriate range, the jobs table must be referenced.

USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'employee_insupd' AND type = 'TR') DROP TRIGGER employee_insupdGOCREATE TRIGGER employee_insupdON employeeFOR INSERT, UPDATEAS/* Get the range of level for this job type from the jobs table. */DECLARE @min_lvl tinyint, @max_lvl tinyint, @emp_lvl tinyint, @job_id smallintSELECT @min_lvl = min_lvl, @max_lvl = max_lvl, @emp_lvl = i.job_lvl, @job_id = i.job_idFROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id JOIN jobs j ON j.job_id = i.job_idIF (@job_id = 1) and (@emp_lvl <> 10) BEGIN RAISERROR ('Job id 1 expects the default level of 10.', 16, 1) ROLLBACK TRANSACTIONENDELSEIF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)BEGIN RAISERROR ('The level for job_id:%d should be between %d and %d.',

16, 1, @job_id, @min_lvl, @max_lvl) ROLLBACK TRANSACTIONEND

D. Use deferred name resolution

This example creates two triggers to illustrate deferred name resolution.

USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'trig1' AND type = 'TR') DROP TRIGGER trig1GO-- Creating a trigger on a nonexistent table.CREATE TRIGGER trig1on authorsFOR INSERT, UPDATE, DELETEAS SELECT a.au_lname, a.au_fname, x.info FROM authors a INNER JOIN does_not_exist x ON a.au_id = x.au_idGO-- Here is the statement to actually see the text of the trigger.SELECT o.id, c.textFROM sysobjects o INNER JOIN syscomments c ON o.id = c.idWHERE o.type = 'TR' and o.name = 'trig1'

-- Creating a trigger on an existing table, but with a nonexistent -- column.USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'trig2' AND type = 'TR') DROP TRIGGER trig2GOCREATE TRIGGER trig2 ON authorsFOR INSERT, UPDATEAS DECLARE @fax varchar(12) SELECT @fax = phone FROM authorsGO-- Here is the statement to actually see the text of the trigger.SELECT o.id, c.textFROM sysobjects o INNER JOIN syscomments c ON o.id = c.idWHERE o.type = 'TR' and o.name = 'trig2'

E. Use COLUMNS_UPDATED

This example creates two tables: an employeeData table and an auditEmployeeData table. The employeeData table, which holds sensitive employee payroll information, can be modified by members of the human resources department. If the employee's social security number (SSN), yearly salary, or bank account number is changed, an audit record is generated and inserted into the auditEmployeeData audit table.

By using the COLUMNS_UPDATED() function, it is possible to test quickly for any changes to these columns that contain sensitive employee information. This use of COLUMNS_UPDATED() only works if you are trying to detect changes to the first 8 columns in the table.

USE pubsIF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'employeeData') DROP TABLE employeeDataIF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'auditEmployeeData') DROP TABLE auditEmployeeDataGOCREATE TABLE employeeData ( emp_id int NOT NULL, emp_bankAccountNumber char (10) NOT NULL, emp_salary int NOT NULL, emp_SSN char (11) NOT NULL, emp_lname nchar (32) NOT NULL, emp_fname nchar (32) NOT NULL, emp_manager int NOT NULL )GOCREATE TABLE auditEmployeeData ( audit_log_id uniqueidentifier DEFAULT NEWID(), audit_log_type char (3) NOT NULL, audit_emp_id int NOT NULL, audit_emp_bankAccountNumber char (10) NULL, audit_emp_salary int NULL, audit_emp_SSN char (11) NULL, audit_user sysname DEFAULT SUSER_SNAME(), audit_changed datetime DEFAULT GETDATE() )GOCREATE TRIGGER updEmployeeData ON employeeData FOR update AS/*Check whether columns 2, 3 or 4 has been updated. If any or all of columns 2, 3 or 4 have been changed, create an audit record. The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To check if all columns 2, 3, and 4 are updated, use = 14 in place of >0 (below).*/

IF (COLUMNS_UPDATED() & 14) > 0/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3, and 4 are updated.*/ BEGIN-- Audit OLD record. INSERT INTO auditEmployeeData (audit_log_type, audit_emp_id, audit_emp_bankAccountNumber, audit_emp_salary, audit_emp_SSN) SELECT 'OLD', del.emp_id, del.emp_bankAccountNumber, del.emp_salary, del.emp_SSN FROM deleted del

-- Audit NEW record. INSERT INTO auditEmployeeData (audit_log_type, audit_emp_id, audit_emp_bankAccountNumber, audit_emp_salary, audit_emp_SSN) SELECT 'NEW', ins.emp_id, ins.emp_bankAccountNumber, ins.emp_salary, ins.emp_SSN FROM inserted ins ENDGO

/*Inserting a new employee does not cause the UPDATE trigger to fire.*/INSERT INTO employeeData VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32)GO

/*Updating the employee record for employee number 101 to change the salary to 51000 causes the UPDATE trigger to fire and an audit trail to be produced.*/

UPDATE employeeData SET emp_salary = 51000 WHERE emp_id = 101GOSELECT * FROM auditEmployeeDataGO

/*Updating the employee record for employee number 101 to change both the bank account number and social security number (SSN) causes the UPDATE trigger to fire and an audit trail to be produced.*/

UPDATE employeeData SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M' WHERE emp_id = 101GOSELECT * FROM auditEmployeeDataGO

F. Use COLUMNS_UPDATED to test more than 8 columns

If you must test for updates that affect columns other than the first 8 columns in a table, you must use the SUBSTRING function to test the proper bit returned by COLUMNS_UPDATED. This example tests for updates that affect columns 3, 5, or 9 in the Northwind.dbo.Customers table.

USE NorthwindDROP TRIGGER tr1GOCREATE TRIGGER tr1 ON CustomersFOR UPDATE AS IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1)) + power(2,(5-1)))

AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1))) ) PRINT 'Columns 3, 5 and 9 updated'GO

UPDATE Customers SET ContactName=ContactName, Address=Address, Country=CountryGO