View:
[AdventureWorks2016CTP3].[dbo].[AUDIT_UNDO]
Name | Value |
Schema | [dbo] |
Owner | [dbo] |
Creation date | 21.09.2016 |
Is schema bound | |
Encrypted | |
ID | 1323867783 |
Name | Value |
QUOTED_IDENTIFIER | ON |
ANSI_NULLS | ON |
Name | Description | Data type | Max length |
AUDIT_LOG_TRANSACTION_ID | int | 4 | |
TABLE_NAME | nvarchar | 261 | |
TABLE_SCHEMA | nvarchar | 261 | |
ACTION_NAME | varchar | 6 | |
HOST_NAME | varchar | 128 | |
APP_NAME | varchar | 128 | |
MODIFIED_BY | varchar | 128 | |
MODIFIED_DATE | datetime | 8 | |
AFFECTED_ROWS | int | 4 | |
AUDIT_LOG_DATA_ID | int | 4 | |
PRIMARY_KEY | nvarchar | 1500 | |
COL_NAME | nvarchar | 128 | |
OLD_VALUE | nvarchar | 4000 | |
NEW_VALUE | nvarchar | 4000 | |
DATA_TYPE | char | 1 |
Object name | Object type | Dep level |
[dbo].[AUDIT_prc_DeleteArchitecture] | Procedure | 1 |
Object name | Object type | Dep level |
[dbo].[AUDIT_LOG_DATA] | Table | 1 |
[dbo].[AUDIT_LOG_TRANSACTIONS] | Table | 1 |
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO CREATE VIEW dbo.AUDIT_UNDO AS /* ------------------------------------------------------------ VIEW: AUDIT_UNDO DESCRIPTION: Selects Audit Log records and returns all rows from the AUDIT_LOG_TRANSACTIONS ALT, with the matching rows in the AUDIT_LOG_DATA AD. ------------------------------------------------------------ */ SELECT ALT.AUDIT_LOG_TRANSACTION_ID, TABLE_NAME = ALT.TABLE_NAME, TABLE_SCHEMA = ALT.TABLE_SCHEMA, CASE WHEN ALT.AUDIT_ACTION_ID = 3 THEN 'Delete' WHEN ALT.AUDIT_ACTION_ID = 2 THEN 'Insert' WHEN ALT.AUDIT_ACTION_ID = 1 THEN 'Update' END AS ACTION_NAME, ALT.HOST_NAME, ALT.APP_NAME, ALT.MODIFIED_BY, ALT.MODIFIED_DATE, ALT.AFFECTED_ROWS, AUDIT_LOG_DATA_ID, PRIMARY_KEY, COL_NAME, OLD_VALUE, NEW_VALUE, DATA_TYPE FROM AUDIT_LOG_TRANSACTIONS ALT LEFT JOIN AUDIT_LOG_DATA AD ON AD.AUDIT_LOG_TRANSACTION_ID = ALT.AUDIT_LOG_TRANSACTION_ID GO |