Vedat Pala
2015-07-07 21:30:33 UTC
I just want to see changing values.
I have got 3 fields.
1.field [OldContent] [xml] NULL,
2.field [NewContent] [xml] NULL,
3.fiedl [UpdateContent] [xml] NULL,
I want to compare the data in two fields.
Results I want to write to the 3.field.([OldContent])
How can I do it in SQL server?
[OldContent] [xml] NULL, in value
<row GUEST_ID="13" GUEST_NAME="VEDAT" GUEST_SURNAME="PALA" ADRESS="IZMIR" />
[NewContent] [xml] NULL, in value
<row GUEST_ID="13" GUEST_NAME="VEDAT" GUEST_SURNAME="PALA" ADRESS="IZMIR" CITY="DR" CITY_CODE="35" />
[UpdateContent] [xml] NULL, I want to write the value in it.
<row CITY="DR" CITY_CODE="35" /
1)now table status
Loading Image...
2)i want this
Loading Image...
I keep a log transformation of the table.
This triger
USE [DENEME]
GO
/****** Object: Trigger [dbo].[iudt_AutoAuditChanges] Script Date: 08.07.2015 00:22:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[iudt_AutoAuditChanges]
ON [dbo].[GUESTS]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
declare @AuditType char(1) /* BEN EKELEDM*/
Declare @v_AuditID bigint
/******************************/
if exists (select * from inserted)
if exists (select * from deleted)
SET @AuditType = 'U'
else
SET @AuditType = 'I'
else
SET @AuditType = 'D'
/******************************/
IF OBJECT_ID('dbo.AutoAudit','U') IS NULL BEGIN
CREATE TABLE [dbo].[AutoAudit]
( [AuditID] bigint identity,
[AuditType] Char(1),
[AuditDate] DateTime,
[AuditUserName] varchar(128),
[TableName] varchar(128) NULL,
[OldContent] XML NULL,
[NewContent] XML NULL
)
ALTER TABLE dbo.AutoAudit ADD CONSTRAINT
PK_AutoAudit PRIMARY KEY CLUSTERED
(
[AuditID]
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [idx_AutoAudit_TableName_AuditDate] ON [dbo].[AutoAudit]
( [TableName] ASC,
[AuditDate] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
Select * Into #AuditDeleted from deleted
Select * Into #AuditInserted from inserted
While (Select COUNT(*) from #AuditDeleted) > 0 OR (Select COUNT(*) from #AuditInserted) > 0
Begin
INSERT INTO [dbo].[AutoAudit]
( [AuditDate],[AuditType], [AuditUserName], [TableName], [OldContent], [NewContent])
SELECT
GETDATE(),
@AuditType,
SUSER_NAME(),
[TableName]=object_name([parent_obj]),
[OldContent]=CAST((SELECT TOP 1 * FROM #AuditDeleted D FOR XML RAW) AS xml),
[NewContent]=CAST((SELECT TOP 1 * FROM #AuditInserted I FOR XML RAW) AS xml)
FROM sysobjects
WHERE
[xtype] = 'tr'
and [name] = OBJECT_NAME(@@PROCID)
Set @v_AuditID = SCOPE_IDENTITY()
Delete from AutoAudit
Where AuditID = @v_AuditID
AND Convert(varchar(max),oldContent) = Convert(varchar(max),NewContent)
Delete top(1) from #AuditDeleted
Delete top(1) from #AuditInserted
End
END
I have got 3 fields.
1.field [OldContent] [xml] NULL,
2.field [NewContent] [xml] NULL,
3.fiedl [UpdateContent] [xml] NULL,
I want to compare the data in two fields.
Results I want to write to the 3.field.([OldContent])
How can I do it in SQL server?
[OldContent] [xml] NULL, in value
<row GUEST_ID="13" GUEST_NAME="VEDAT" GUEST_SURNAME="PALA" ADRESS="IZMIR" />
[NewContent] [xml] NULL, in value
<row GUEST_ID="13" GUEST_NAME="VEDAT" GUEST_SURNAME="PALA" ADRESS="IZMIR" CITY="DR" CITY_CODE="35" />
[UpdateContent] [xml] NULL, I want to write the value in it.
<row CITY="DR" CITY_CODE="35" /
1)now table status
Loading Image...
2)i want this
Loading Image...
I keep a log transformation of the table.
This triger
USE [DENEME]
GO
/****** Object: Trigger [dbo].[iudt_AutoAuditChanges] Script Date: 08.07.2015 00:22:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[iudt_AutoAuditChanges]
ON [dbo].[GUESTS]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
declare @AuditType char(1) /* BEN EKELEDM*/
Declare @v_AuditID bigint
/******************************/
if exists (select * from inserted)
if exists (select * from deleted)
SET @AuditType = 'U'
else
SET @AuditType = 'I'
else
SET @AuditType = 'D'
/******************************/
IF OBJECT_ID('dbo.AutoAudit','U') IS NULL BEGIN
CREATE TABLE [dbo].[AutoAudit]
( [AuditID] bigint identity,
[AuditType] Char(1),
[AuditDate] DateTime,
[AuditUserName] varchar(128),
[TableName] varchar(128) NULL,
[OldContent] XML NULL,
[NewContent] XML NULL
)
ALTER TABLE dbo.AutoAudit ADD CONSTRAINT
PK_AutoAudit PRIMARY KEY CLUSTERED
(
[AuditID]
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [idx_AutoAudit_TableName_AuditDate] ON [dbo].[AutoAudit]
( [TableName] ASC,
[AuditDate] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
Select * Into #AuditDeleted from deleted
Select * Into #AuditInserted from inserted
While (Select COUNT(*) from #AuditDeleted) > 0 OR (Select COUNT(*) from #AuditInserted) > 0
Begin
INSERT INTO [dbo].[AutoAudit]
( [AuditDate],[AuditType], [AuditUserName], [TableName], [OldContent], [NewContent])
SELECT
GETDATE(),
@AuditType,
SUSER_NAME(),
[TableName]=object_name([parent_obj]),
[OldContent]=CAST((SELECT TOP 1 * FROM #AuditDeleted D FOR XML RAW) AS xml),
[NewContent]=CAST((SELECT TOP 1 * FROM #AuditInserted I FOR XML RAW) AS xml)
FROM sysobjects
WHERE
[xtype] = 'tr'
and [name] = OBJECT_NAME(@@PROCID)
Set @v_AuditID = SCOPE_IDENTITY()
Delete from AutoAudit
Where AuditID = @v_AuditID
AND Convert(varchar(max),oldContent) = Convert(varchar(max),NewContent)
Delete top(1) from #AuditDeleted
Delete top(1) from #AuditInserted
End
END