trigger

Espero sirva, yo tengo implementado este TRIGGER en todas
mis tablas para hacer una especie de AUDITORIA:

CREATE PROCEDURE pr_instalarTriggerAudit

@tabla as char(128)

AS

SET NOCOUNT ON

DECLARE
@SQLString NVARCHAR(1200)

para asegurar la atomicidad
BEGIN TRAN

SET @SQLString = N'CREATE TRIGGER trgarea_' + ltrim(rtrim
(upper( @tabla ))) +' ON ' + ltrim(rtrim(upper(@tabla))) +
' FOR DELETE, INSERT, UPDATE AS ' + CHAR(10) +
'SET NOCOUNT ON ' + CHAR(10) + CHAR(10) +
'DECLARE ' + CHAR(10) +
' @pcAccion CHAR(6)' + CHAR(10) +
CHAR(10) +
'SELECT @pcAccion = CASE ' + CHAR(10) +
' WHEN ( SELECT COUNT(*) FROM deleted ) > 0
THEN ' + CHAR(10) +
' CASE ' + CHAR(10) +
' WHEN ( SELECT COUNT(*)
FROM inserted ) > 0 THEN ''UPDATE''' + CHAR(10) +
' ELSE ''DELETE''' + CHAR
(10) +
' end ' + CHAR(10) +
' ELSE ' + CHAR(10) +
' CASE ' + CHAR(10) +
' WHEN ( SELECT COUNT(*)
FROM inserted ) > 0 THEN ''INSERT''' + CHAR(10) +
' else ''*''' + CHAR(10) +
' end ' + CHAR(10) +
' END ' + CHAR(10) +
CHAR(10) +
'IF NOT EXISTS( SELECT UR_SPID FROM USER_REGISTER
WHERE UR_SPID = @@SPID )
BEGIN
SELECT -9, ''El usuario no esta registrado
en el sistema.''
ROLLBACK TRANSACTION
END' + CHAR(10) +
CHAR(10) +
'INSERT INTO AUDIT( UR_SPID, cAuditTipo_Eevento,
cUsuarioAlias, dAuditFechaRegistro, cAuditDescripcion,
cAuditTerminal, cAuditAplicacion ) ' + CHAR(10) +
'SELECT CAST( @@SPID AS VARCHAR ),
@pcAccion,
cUsuarioAlias,
GETDATE(), ' + CHAR(10) + '''' +
LTRIM( RTRIM( UPPER( @tabla )) ) + ' - ''
+ @pcAccion + '' de un registro'',
cAuditTerminal,
''SQL Execute''
FROM USER_REGISTER
WHERE UR_SPID = CAST(@@SPID AS VARCHAR) '

EXECUTE sp_executesql @SQLString

COMMIT TRAN