The script bellow, creates a test database, and a table named "acesso" with a trigger that logs all changes made to the "acesso" table into a historic table named "acessohist".
Please note that all changes are logged in as a XML value. That is because a tadicional logging table might be a mantainance issue, because, once you have a brand new column (or change a columns data type or name, for that matter) in your database, you might need to review the log table and triggers.
Of course, this is for all of you that use SQL SERVER Standard edition, because on Enterprise, you have CDC.
USE [master]
go
/****** Table Log Example ******/
CREATE DATABASE [dbTestTrigger]
go
USE [dbTestTrigger]
go
CREATE TABLE [dbo].acesso
(
[superior] [VARCHAR](120) NOT NULL,
[propriedade] [VARCHAR](120) NOT NULL,
[descricao] [VARCHAR](140) NULL,
[especial] [CHAR](1) NOT NULL DEFAULT ('F'),
[idacesso] [INT] IDENTITY(1, 1) NOT NULL,
PRIMARY KEY CLUSTERED ( [idacesso] ASC )
)
ON [PRIMARY]
go
CREATE TABLE [dbo].[acessohist]
(
[idacessohist] [INT] IDENTITY(1, 1) NOT NULL,
[data] [DATETIME] NULL DEFAULT (Getdate()),
[texto] [XML] NULL,
PRIMARY KEY CLUSTERED ( [idacessohist] ASC )
)
ON [PRIMARY]
textimage_on [PRIMARY]
go
CREATE TRIGGER trglogaccessdef
ON [dbo].[acesso]
after INSERT, DELETE, UPDATE
AS
BEGIN
SET nocount ON;
DECLARE @Text XML;
SET @Text = (SELECT *,
SYSTEM_USER AS LoginName
FROM deleted
FOR xml auto)
IF @Text IS NOT NULL
BEGIN
INSERT INTO [dbo].[acessohist]
(texto)
VALUES (@Text)
END
SET @Text = NULL
SET @Text = (SELECT *,
SYSTEM_USER AS LoginName
FROM inserted
FOR xml auto)
IF @Text IS NOT NULL
BEGIN
INSERT INTO [dbo].[acessohist]
(texto)
VALUES (@Text)
END
END
go
go
/****** Table Log Example ******/
CREATE DATABASE [dbTestTrigger]
go
USE [dbTestTrigger]
go
CREATE TABLE [dbo].acesso
(
[superior] [VARCHAR](120) NOT NULL,
[propriedade] [VARCHAR](120) NOT NULL,
[descricao] [VARCHAR](140) NULL,
[especial] [CHAR](1) NOT NULL DEFAULT ('F'),
[idacesso] [INT] IDENTITY(1, 1) NOT NULL,
PRIMARY KEY CLUSTERED ( [idacesso] ASC )
)
ON [PRIMARY]
go
CREATE TABLE [dbo].[acessohist]
(
[idacessohist] [INT] IDENTITY(1, 1) NOT NULL,
[data] [DATETIME] NULL DEFAULT (Getdate()),
[texto] [XML] NULL,
PRIMARY KEY CLUSTERED ( [idacessohist] ASC )
)
ON [PRIMARY]
textimage_on [PRIMARY]
go
CREATE TRIGGER trglogaccessdef
ON [dbo].[acesso]
after INSERT, DELETE, UPDATE
AS
BEGIN
SET nocount ON;
DECLARE @Text XML;
SET @Text = (SELECT *,
SYSTEM_USER AS LoginName
FROM deleted
FOR xml auto)
IF @Text IS NOT NULL
BEGIN
INSERT INTO [dbo].[acessohist]
(texto)
VALUES (@Text)
END
SET @Text = NULL
SET @Text = (SELECT *,
SYSTEM_USER AS LoginName
FROM inserted
FOR xml auto)
IF @Text IS NOT NULL
BEGIN
INSERT INTO [dbo].[acessohist]
(texto)
VALUES (@Text)
END
END
go
Nenhum comentário:
Postar um comentário
Leave your comment here!