View: [AdventureWorks2016CTP3].[dbo].[AUDIT_UNDO]

CollapseAll image

Collapse image View properties


Name  Value 
 Schema   [dbo] 
 Owner   [dbo] 
 Creation date   21.09.2016 
 Is schema bound   
 Encrypted   
 ID   1323867783 

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image 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
Total: 15 column(s)

Collapse image Objects that depend on [dbo].[AUDIT_UNDO]


Object name Object type Dep level
[dbo].[AUDIT_prc_DeleteArchitecture] Procedure 1
Total 1 object(s)

Collapse image 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
Total 2 object(s)

Collapse image 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

Collapse image See also


List of views