Guia Para Creación De Triggers En Sql-Server

GUIA PARA CREACIÓN DE TRIGGERS EN SQL-SERVER

Triggers en SQL Server

Sintaxis

CREATE TRIGGER nombreDesencadenador
ON tabla
[WITH ENCRYPTION]
{
{FOR { [DELETE] [,] [INSERT] [,][UPDATE] }
[WITH APPEND]
[NOT FOR REPLICATION]
AS
instrucciónSQL [...n]

}
|
{FOR { [INSERT] [,] [UPDATE] }
[WITH APPEND]
[NOT FORREPLICATION]
AS
{ IF UPDATE (columna)
[{AND | OR} UPDATE (columna)]
[...n]
| IF (COLUMNS_UPDATED() {operadorNivelBit}máscaraBitsActualizada)
{ operadorComparación} máscaraBitsColumna [...n]
}
instrucciónSQL [...n]

}
}

Ejemplo:

El siguiente desencadenador de ejemplo imprime unmensaje en el cliente cuando alguien intenta agregar o cambiar datos en la tabla titles de la base de datos Pubs.

USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder'AND type = 'TR')
DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE
AS RAISERROR (50009, 16, 10)
GO

Otro Ejemplo de un trigger asociado a la tabla employee dela BD Pubs

IF OBJECT_ID('dbo.employee_insupd') IS NOT NULL
BEGIN
DROP TRIGGER dbo.employee_insupd
IF OBJECT_ID('dbo.employee_insupd') IS NOT NULL
PRINT '>'
ELSEPRINT '>'
END
go
CREATE TRIGGER employee_insupd
ON employee
FOR insert, UPDATE
AS
--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 smallint

select @min_lvl = min_lvl,
@max_lvl = max_lvl,
@emp_lvl = i.job_lvl,
@job_id =i.job_id
from employee e,
jobs j,
inserted I --- Tabla Temporal de SQL en donde se almacenan los valores a insertar
where e.emp_id = i.emp_id
AND i.job_id =...