View:
[AdventureWorks2016CTP3].[dbo].[AUDIT_UNDO]
View properties
| Name | Value |
| Schema | [dbo] |
| Owner | [dbo] |
| Creation date | 21.09.2016 |
| Is schema bound | |
| Encrypted | |
| ID | 1323867783 |
Creation options
| Name | Value |
| QUOTED_IDENTIFIER | ON |
| ANSI_NULLS | ON |
Resultset
| 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 |
Objects that depend on [dbo].[AUDIT_UNDO]
| Object name | Object type | Dep level |
| [dbo].[AUDIT_prc_DeleteArchitecture] | Procedure | 1 |
Objects that [dbo].[AUDIT_UNDO] depends on
| Object name | Object type | Dep level |
| [dbo].[AUDIT_LOG_DATA] | Table | 1 |
| [dbo].[AUDIT_LOG_TRANSACTIONS] | Table | 1 |
SQL
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 |
See also