트리거는 SQL 영향을받는 테이블이 로그 만들기를?

나는 열의 값을 업데이트되고 있는지 확인하기 위해 노력하고있어 그리고 난 응용 프로그램의 아주 작은 지식을 가지고. 빠른 눈에 나는 응용 프로그램의 비즈니스 로직의 90 %가 데이터베이스에 처리에 대해 나타났습니다. 도없이 SP의, 함수 및 트리거의 깊이가 미친라고합니다.

나는 테이블을 영향을받는 SQL을 기록합니다 문제의 테이블에 트리거를 작성하고 싶습니다. SQL 테이블의 맥락에서 실행 된 SQL을 잡기 위해 사용될 수있는 어떤 업데이트되는?

세부: MS SQL 서버 2008

감사!!

해결법

==============================

1.나는이 문제가 이미 해결되었습니다하지만 나는 그것이 SQL 서버 2008 확장 이벤트를 사용하여 해결 될 수있는 방법에 관심이 실현. 내가 개선하기 위해 많이있을거야, 그래서이 XEvents 내 첫 플레이입니다!

나는이 문제가 이미 해결되었습니다하지만 나는 그것이 SQL 서버 2008 확장 이벤트를 사용하여 해결 될 수있는 방법에 관심이 실현. 내가 개선하기 위해 많이있을거야, 그래서이 XEvents 내 첫 플레이입니다!

설정 테스트 데이터베이스에 스크립트

CREATE TABLE [dbo].[TableWithMysteryUpdate](
    [Period] [int] NOT NULL,
    [ColumnThatWillBeUpdated] [int]  NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TableWithMysteryUpdate] ([Period], [ColumnThatWillBeUpdated]) VALUES (1, 20)
INSERT [dbo].[TableWithMysteryUpdate] ([Period], [ColumnThatWillBeUpdated]) VALUES (2, 23)

GO

CREATE TABLE [dbo].[TestTable](
    [foo] [int] IDENTITY(1,1) NOT NULL,
    [bar] [nchar](10) NOT NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
    [foo] ASC
)
)
GO

CREATE TRIGGER [dbo].[triggerCausingMysteryUpdate] 
   ON  [dbo].[TestTable] 
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
UPDATE [dbo].[TableWithMysteryUpdate]
   SET [Period] = [Period]+1

END
GO

CREATE PROCEDURE [dbo].[Proc4]
AS
BEGIN
INSERT INTO [dbo].[TestTable]
           (
           [bar])
     VALUES
           ('Test')
END

GO

CREATE PROCEDURE [dbo].[Proc3]
AS
BEGIN
EXEC dbo.Proc4
END
GO

CREATE PROCEDURE [dbo].[Proc2]
AS
BEGIN
EXEC dbo.Proc3
END
GO

CREATE PROCEDURE [dbo].[Proc1]
AS
BEGIN
EXEC dbo.Proc2
END

그래서 시나리오는 TableWithMysteryUpdate가 업데이트되고 있지만 무엇에 의해 잘 모르겠어요 것입니다. 나는이 개체를 필터링 할 수 있도록하기 위해 아무것도하지 않는 업데이트 트리거를 추가합니다.

CREATE TRIGGER [dbo].[triggerAfterUpdate] 
   ON  [dbo].[TableWithMysteryUpdate] 
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

END

그리고 결국 호출 스택 아래로 업데이트가 발생하고 세션을 중지하는 것입니다 원인 절차를 화재의 XEvents 세션을 생성하는 스크립트를 실행합니다.

USE TestDB    

DECLARE @DynSql nvarchar(max)

SET @DynSql = '

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=''test_trace'')
    DROP EVENT SESSION [test_trace] ON SERVER;
CREATE EVENT SESSION [test_trace]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
     ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack)
     WHERE (object_id = ' + cast(object_id('[dbo].[triggerAfterUpdate]') as varchar(10)) + ') 
     )
,
ADD EVENT sqlserver.sp_statement_completed(
     ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack)
     WHERE (object_id = ' + cast(object_id('[dbo].[triggerAfterUpdate]') as varchar(10)) + ') 
     )
ADD TARGET package0.asynchronous_file_target
(set filename = ''c:\temp\test_trace.xel'' , metadatafile = ''c:\temp\test_trace.xem'')
ALTER EVENT SESSION [test_trace] ON SERVER STATE = START
'

EXEC sp_executesql @DynSql
GO

EXEC  dbo.Proc1

GO

ALTER EVENT SESSION [test_trace] ON SERVER STATE = STOP

추적 데이터를 읽을 수 있습니다

SELECT CONVERT (XML, event_data) AS data
        FROM sys.fn_xe_file_target_read_file ('C:\Temp\test_trace*.xel', 'C:\Temp\test_trace*.xem', NULL, NULL)

호출 스택의 일부입니다

 <action name="tsql_stack" package="sqlserver">
    <value>&lt;frame level='1' handle='0x03000800E8EA0D0612E4EB00A59D00000000000000000000' line='6' offsetStart='228' offsetEnd='264'/&gt;
&lt;frame level='2' handle='0x03000800921155002C81E700A59D00000000000000000000' line='8' offsetStart='258' offsetEnd='398'/&gt;
&lt;frame level='3' handle='0x03000800CB3549012F81E700A59D00000100000000000000' line='5' offsetStart='90' offsetEnd='284'/&gt;
&lt;frame level='4' handle='0x03000800045A3D022F81E700A59D00000100000000000000' line='5' offsetStart='90' offsetEnd='120'/&gt;
&lt;frame level='5' handle='0x030008003D7E31033081E700A59D00000100000000000000' line='5' offsetStart='90' offsetEnd='120'/&gt;
&lt;frame level='6' handle='0x0300080076A225043081E700A59D00000100000000000000' line='5' offsetStart='90' offsetEnd='120'/&gt;
&lt;frame level='7' handle='0x010008002E775203603D9A0D000000000000000000000000' line='2' offsetStart='4' offsetEnd='-1'/&gt;</value>
    <text />
  </action>

DMV에에 가입

WITH CapturedResults AS
    ( SELECT data.value ( '(/event/@timestamp)[1]', 'DATETIME')                                        AS [TIME],
        data.value ( '(/event/data[@name=''cpu'']/value)[1]', 'INT')                                   AS [CPU (ms)],
        CONVERT (FLOAT, data.value ('(/event/data[@name=''duration'']/value)[1]', 'BIGINT')) / 1000000 AS [Duration (s)],
        data.value ( '(/event/action[@name=''sql_text'']/value)[1]', 'VARCHAR(MAX)')                   AS [SQL STATEMENT],
        CAST(data.value('(/event/action[@name="tsql_stack"]/value)[1]','varchar(MAX)') AS XML)         AS [stack_xml]
    FROM (SELECT CONVERT (XML, event_data) AS                                                             data
        FROM sys.fn_xe_file_target_read_file ('C:\Temp\test_trace*.xel', 'C:\Temp\test_trace*.xem', NULL, NULL)
        )
        entries
    )
    ,
    StackData AS
    ( SELECT frame_xml.value('(./@level)', 'int')      AS [frame_level],
        frame_xml.value('(./@handle)', 'varchar(MAX)') AS [sql_handle],
        frame_xml.value('(./@offsetStart)', 'int')     AS [offset_start],
        frame_xml.value('(./@offsetEnd)', 'int')       AS [offset_end]
    FROM CapturedResults CROSS APPLY stack_xml.nodes('//frame') N (frame_xml)
    )
SELECT sd.frame_level,
    object_name(st.objectid, st.dbid) AS ObjectName,
    SUBSTRING(st.text, (sd.offset_start/2)+1, ((
            CASE sd.offset_end
                WHEN -1
                THEN DATALENGTH(st.text)
                ELSE sd.offset_end
            END - sd.offset_start)/2) + 1) AS statement_text,
    qp.query_plan,
    qs2.creation_time,
    qs2.last_execution_time,
    qs2.execution_count
FROM StackData AS sd CROSS APPLY sys.dm_exec_sql_text(CONVERT(VARBINARY(MAX),sd.sql_handle,1)) AS st
    LEFT OUTER JOIN sys.dm_exec_query_stats qs2
    ON  qs2.sql_handle = CONVERT(VARBINARY(MAX),sd.sql_handle,1) OUTER APPLY sys.dm_exec_query_plan(CONVERT(VARBINARY(MAX),qs2.plan_handle,1)) AS qp

(전체 TSQL 호출 스택을 나타내는) 검색 결과를 제공

==============================
2.데이터 변경이 발생하는 경우 해고 될 트리거를 사용하여 게다가, 당신은 SQL 서버 추적을 사용할 수 있으며 SQL 프로파일 러, 또는 DML에 대한 트랙 실행 코드를 변경하는 타사 감사 도구를 분석 할 수 있습니다.

데이터 변경이 발생하는 경우 해고 될 트리거를 사용하여 게다가, 당신은 SQL 서버 추적을 사용할 수 있으며 SQL 프로파일 러, 또는 DML에 대한 트랙 실행 코드를 변경하는 타사 감사 도구를 분석 할 수 있습니다.

ApexSQL 당신이 감사에만 특정 데이터베이스 개체 (이 경우 테이블) 및 이벤트 유형을 좁힐 수 있습니다 준수하여.

보고서는 변화를 시작 실행 된 SQL 문 등 다양한 유용한 정보를 보여줍니다.

면책 조항 : 기술 지원 엔지니어로 ApexSQL에 대한 I 작업
==============================
3.난 내가 sys.dm_exec_sql_text AND가 sys.dm_exec_query_stats에서 타임 스탬프를 통해 좁혀 기록 된 정보를 해당 테이블에 트리거를 추가했다. 이것은 신속하게 내가 무엇을 찾고 있었다 아래로 못을 박았다. 거기 턴은이 UPDATE 후 데이터를 업데이트했다 대해 내가 모르는 몇 가지 트리거했다.

난 내가 sys.dm_exec_sql_text AND가 sys.dm_exec_query_stats에서 타임 스탬프를 통해 좁혀 기록 된 정보를 해당 테이블에 트리거를 추가했다. 이것은 신속하게 내가 무엇을 찾고 있었다 아래로 못을 박았다. 거기 턴은이 UPDATE 후 데이터를 업데이트했다 대해 내가 모르는 몇 가지 트리거했다.
```
SELECT 
    qStats.last_execution_time AS [ExecutedAt],
    qTxt.[text] AS [Query], qTxt.number
FROM
    sys.dm_exec_query_stats AS qStats
    CROSS APPLY
    sys.dm_exec_sql_text(qStats.sql_handle) AS qTxt
WHERE
    qTxt.[dbid] = @DbId
    AND qTxt.[text] like '%UPDATE%'
    AND qStats.last_execution_time between @StartExecutionSearchTime and @EndExecutionSearchTime
ORDER BY
    qStats.last_execution_time DESC 
```
==============================
4.이 연결 Microsoft 항목에 대한 투표를,이 호출 스택의 해결에서 참조하시기 바랍니다.

이 연결 Microsoft 항목에 대한 투표를,이 호출 스택의 해결에서 참조하시기 바랍니다.
==============================
5.SQL Server 2008의 새로운 변경 데이터 캡처 (CDC)라는 기능을 사용 하는것보다 트리거를 소개했다. 여기에 대해 자세히 알아보십시오.

SQL Server 2008의 새로운 변경 데이터 캡처 (CDC)라는 기능을 사용 하는것보다 트리거를 소개했다. 여기에 대해 자세히 알아보십시오.

from https://stackoverflow.com/questions/3094735/create-trigger-to-log-sql-that-affected-table by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 준비된 문에 LIKE '% {$ var에} %'를 사용하는 올바른 방법은? [mysqli] (0)	2020.04.05
[SQL] SQL은 - 행에서 파괴 값을 빼면 (0)	2020.04.05
[SQL] BigQuery에 열로 트랜스 행 (피벗 구현) (0)	2020.04.05
[SQL] 이유는 무엇입니까이 SQL 코드를주고 오류 1066 (안 독특한 테이블 / 별칭 : '사용자')? (0)	2020.04.05
[SQL] 테이블 필드는 하이픈을 포함 할 수 있습니까? (0)	2020.04.05

복붙노트

[SQL] 트리거는 SQL 영향을받는 테이블이 로그 만들기를?

트리거는 SQL 영향을받는 테이블이 로그 만들기를?

해결법

1.나는이 문제가 이미 해결되었습니다하지만 나는 그것이 SQL 서버 2008 확장 이벤트를 사용하여 해결 될 수있는 방법에 관심이 실현. 내가 개선하기 위해 많이있을거야, 그래서이 XEvents 내 첫 플레이입니다!

2.데이터 변경이 발생하는 경우 해고 될 트리거를 사용하여 게다가, 당신은 SQL 서버 추적을 사용할 수 있으며 SQL 프로파일 러, 또는 DML에 대한 트랙 실행 코드를 변경하는 타사 감사 도구를 분석 할 수 있습니다.

4.이 연결 Microsoft 항목에 대한 투표를,이 호출 스택의 해결에서 참조하시기 바랍니다.

5.SQL Server 2008의 새로운 변경 데이터 캡처 (CDC)라는 기능을 사용 하는것보다 트리거를 소개했다. 여기에 대해 자세히 알아보십시오.

'SQL' 카테고리의 다른 글

티스토리툴바