Czy można uzyskać stos wywołań wykonawczych w wyzwalaczu?

16

Mam 10 procedur przechowywanych i każda z nich wstawia INSERT do jednej tabeliX.

Czy w ciele wyzwalacza tableX można uzyskać, który obiekt powoduje modyfikację tableX (przechowywany proc1 lub sp2 lub ....)?

Dziękuję Ci.

sql-server-2008 trigger

— garik
źródło

9

Tak, można zidentyfikować działający kod, używając funkcji systemowej @@ procid i lepiej OBJECT_NAME (@@ PROCID), aby mieć pełną nazwę.

Definicja: „Zwraca identyfikator obiektu (ID) bieżącego modułu Transact-SQL. Moduł Transact-SQL może być procedurą składowaną, funkcją zdefiniowaną przez użytkownika lub wyzwalaczem. @@ PROCID nie może być określony w modułach CLR ani w przetwarzaj dostawcę danych ”.

Możesz przeczytać o tym tutaj .

Inną opcją byłoby sprawdzenie planu sql bieżącego spid i zapisanie tych informacji w tabeli rejestrowania. Przykładowe zapytanie, które zostanie użyte w każdej procedurze do zapisania danych audytu, to:

select sp.hostname, sp.program_name, sp.loginame,
    st.text as query_text
from sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) as st  
where sp.spid = @@spid

Może jest tam zbyt wiele szczegółów ... ale wierzę, że masz pomysł.

Trzecią opcją byłoby wykorzystanie informacji kontekstu_informacji do bieżącej sesji SP. I skojarz gdzieś informacje kontekstowe zapisane tam z każdą procedurą. Na przykład w procedurze 1 piszesz 111 w kontekście, w procedurze 2 piszesz 222 .. i tak dalej.

Dużo więcej informacji dotyczących informacji o kontekście można przeczytać w tym pytaniu SO .

— Marian
źródło

1

1) OBJECT_NAME (@@ PROCID) w wyzwalaczu zwraca nazwę wyzwalacza :(. 2) konieczne jest posiadanie informacji tylko przy wyzwalaczu. 3) kontekst_informacji jest rozwiązaniem. dzięki.

— garik

1

Tak, wewnątrz wyzwalacza OBJECT_NAME(@@PROCID)zwraca nazwę wyzwalacza, a nie wywołujący proc.

— ProfK

To jest po prostu źle. Zwraca nazwę wyzwalacza, a nie procedury wywoływania, jak poprosił PO

— Reversed Engineer

Zgadzam się, odpowiedź jest zła. CONTEXT_INFO działa, jeśli możesz zmodyfikować procedurę wysyłania.

— Tom Warfield

3

Też chciałem to zrobić. Dziękuję za odpowiedź. Ponieważ wciąż tu jestem, opublikuję mój test, aby zaoszczędzić czas innym :)

CREATE TABLE  Test ( TestID INT )
GO

CREATE TRIGGER TestTrigger ON Test
FOR INSERT
AS

SELECT CAST(CONTEXT_INFO() AS NVARCHAR(128));
GO

CREATE PROCEDURE usp_ProcIDTest
AS

DECLARE @ProcedureName VARBINARY(MAX) = CAST(OBJECT_NAME(@@PROCID) AS VARBINARY(MAX))
SET CONTEXT_INFO @ProcedureName

INSERT INTO Test ( TestID ) VALUES ( 1 ) 

GO

EXEC usp_ProcIDTest
GO

DROP TABLE Test
GO

— Jim Brown
źródło

2

XEvent zapewnia inny sposób poznania stosu T-SQL, chociaż SQL Server 2008 może nie obsługiwać używanego typu zdarzenia. Rozwiązanie składa się z wyzwalacza, błędu i sesji XEvent. Wziąłem przykład Jima Browna, aby pokazać, jak to działa.

Przede wszystkim przetestowałem rozwiązanie dla SQL Server 2016 SP2CU2 Dev Edition. SQL Server 2008 obsługuje niektóre EXevent, ale nie mam żadnego wystąpienia, więc nie mogłem go przetestować.

Chodzi o to, aby wygenerować błąd użytkownika w fałszywym bloku try-catch, a następnie złapać błąd w sesji XEvent za pomocą tsql_stackakcji. SQLSERVER.error_reportedTyp XEvent może wychwytywać wszystkie błędy, nawet jeśli blok try-catch je uwięził. Na koniec sys.dm_exec_sql_textwyodrębnij zapytania T-SQL z uchwytów zapytań, które tsql_stackdaje akcja.

Przykład odpowiedzi Jima Browna, którą opracowałem, pokazano poniżej. Wyzwalacz podnosi błąd z tekstem „catch me”. Sesja XEvent wychwytuje błędy tylko z tekstem „złap mnie”.

CREATE TABLE  Test ( TestID INT )

GO

CREATE TRIGGER TestTrigger ON Test
FOR INSERT
AS
BEGIN TRY
    SET XACT_ABORT OFF; -- REALLY IMPORTANT!
    /* make an catching a great deal more interesting */
    DECLARE @TestID NVARCHAR(MAX) ;
    SELECT TOP (1) @TestID = CAST(ins.TestID AS NVARCHAR(MAX)) FROM inserted AS ins ;
    RAISERROR (N'catch_me TestID = "%s"' , 11 , 0 , @TestID) ;
END TRY BEGIN CATCH /* NOTHING TO DO */ END CATCH

GO

CREATE PROCEDURE usp_ProcIDTest
AS
INSERT INTO Test ( TestID ) VALUES ( 1 ) 

GO

CREATE PROCEDURE usp_RootProcIDTest
AS
EXEC usp_ProcIDTest

GO

-- This XEvent session definition was kindly provided by XEvent 'New Session' wizard.
CREATE EVENT SESSION [catch_insertion_into_Test] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(package0.callstack,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack,sqlserver.username,sqlserver.context_info,sqlserver.plan_handle)
    WHERE ([message] like N'catch_me%'))
ADD TARGET package0.ring_buffer(SET max_memory=(10240))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

GO

Teraz, jeśli uruchomisz sesję XEvent (SSMS, Eksplorator obiektów, Zarządzanie, Rozszerzone zdarzenia, Sesje, catch_insertion_into_Test), wykonasz usp_RootProcIDTest i przejrzysz bufor pierścieniowy sesji XEvent, powinieneś zobaczyć XML, który składa się z węzła <action name="tsql_stack" package="sqlserver">. Istnieje sekwencja węzłów ramki. Wstaw wartości handleatrybutu do funkcji systemowej sys.dm_exec_sql_text i voilà:

-- REPLACE MY HANDLES WITH YOURS
SELECT * FROM sys.dm_exec_sql_text(0x03000800D153096910272C01A6AA000000000000000000000000000000000000000000000000000000000000);
SELECT * FROM sys.dm_exec_sql_text(0x030008000A78FD6912272C01A6AA000001000000000000000000000000000000000000000000000000000000);
SELECT * FROM sys.dm_exec_sql_text(0x03000800439CF16A13272C01A6AA000001000000000000000000000000000000000000000000000000000000);

XEvent pozwala ci robić znacznie więcej! Nie przegap okazji, aby się ich nauczyć!

— Basil Kisel
źródło