View: [AdventureWorks2016CTP3].[dbo].[AUDIT_VIEW]

CollapseAll image

Collapse image View properties


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

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Resultset


Name  Description Data type Max length
TABLE_NAME   nvarchar 261
ACTION   varchar 6
MODIFIED_BY   varchar 128
PRIMARY_KEY   nvarchar 1500
REC_COUNT   int 4
MODIFIED_DATE   varchar 20
COMPUTER   varchar 128
APPLICATION   varchar 128
Total: 8 column(s)

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


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

Collapse image Objects that [dbo].[AUDIT_VIEW] 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_VIEW AS
/* ------------------------------------------------------------
VIEW:          AUDIT_VIEW
DESCRIPTION:   Selects Audit Log records and groups by MODIFIED_DATE and PK
               effectively grouping audit data by Audit transaction
   ------------------------------------------------------------ */
SELECT MAX(t.TABLE_NAME) AS TABLE_NAME,
    CASE MAX(t.AUDIT_ACTION_ID) 
    WHEN 1 THEN 'UPDATE' 
    WHEN 2 THEN 'INSERT' 
    WHEN 3 THEN 'DELETE' 
    END AS ACTION, 
    MAX(t.MODIFIED_BY) AS MODIFIED_BY, 
    MAX(PRIMARY_KEY_DATA) AS PRIMARY_KEY,
    COUNT(DISTINCT PRIMARY_KEY_DATA) AS REC_COUNT,
    CONVERT(varchar(20), MODIFIED_DATE, 113) AS MODIFIED_DATE,
    Max(HOST_NAME) AS COMPUTER,
    Max(APP_NAME) as APPLICATION
FROM dbo.AUDIT_LOG_TRANSACTIONS t
INNER JOIN dbo.AUDIT_LOG_DATA r ON r.AUDIT_LOG_TRANSACTION_ID = t.AUDIT_LOG_TRANSACTION_ID
GROUP BY MODIFIED_DATE, PRIMARY_KEY_DATA
GO

Collapse image See also


List of views