|
Есть такое хранилище логов
USE [LogDB]
GO
/****** Object: Table [dbo].[tablesForLogging] Script Date: 31.08.2021 10:45:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- конфиг таблицы, что логируем
CREATE TABLE [dbo].[tablesForLogging](
[BaseName] [nvarchar](50) NULL,
[tableName] [nvarchar](50) NULL,
[Field_Key] [nvarchar](100) NULL,
[is_active] [bit] NULL,
[Storage] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tablesForLogging] ADD CONSTRAINT [DF_TablesForLogging_is_active] DEFAULT ((1)) FOR [is_active]
GO
USE [LogDB]
GO
/****** Object: Table [dbo].[tableLoggingData] Script Date: 31.08.2021 10:46:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- сами залогированные данные
CREATE TABLE [dbo].[tableLoggingData](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[Base_ID] [bigint] NOT NULL,
[table_ID] [bigint] NOT NULL,
[DateAdd] [datetime] NOT NULL,
[is_type] [bit] NOT NULL,
[HostName] [varchar](100) NULL,
[ProgramName] [varchar](100) NULL,
[Proc_ID] [bigint] NULL,
[SystemUser] [varchar](50) NULL,
[Str_json] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
GO
Use [SomeAbstractDB]
go
на какой-то абстрактной таблице есть триггер
CREATE TRIGGER [dbo].[LogAction_tt_online_services] ON [dbo].[SomeAbstractTable] AFTER DELETE,UPDATE,INSERT
AS
BEGIN
IF EXISTS (SELECT * FROM deleted)
BEGIN
INSERT INTO LogDB.dbo.TableLoggingData
(Base_ID, Table_ID, [DateAdd], is_type, HostName, ProgramName, Proc_ID, SystemUser, Str_json)
SELECT 23, 485330533, GETDATE(), 1, HOST_NAME(), PROGRAM_NAME(),
@@PROCID, SYSTEM_USER, (SELECT D.* FOR JSON PATH)
FROM deleted D
END
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
BEGIN
INSERT INTO LogDB.dbo.TableLoggingData
(Base_ID, Table_ID, [DateAdd], is_type, HostName, ProgramName, Proc_ID, SystemUser, Str_json)
SELECT 23, 485330533, GETDATE(), 0, HOST_NAME(), PROGRAM_NAME(),
@@PROCID, SYSTEM_USER, (SELECT I.* FOR JSON PATH)
FROM inserted I
END
END
Задача:
1) Сделать процедуру восстановления данных, которая на вход принимает айдишник из таблицы TableLoggingData и в целевой таблице восстанавливает запись
2) Может что-то можно\нужно изменить в триггере ?
|
