info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
Use a SQL Server Trigger to capture calling object updating a table
by BF (Principal Consultant; Architecture; Engineering)
2015-08-06
Solution:
Step 1: Create a new Logging Table CREATE TABLE [dbo].[LOG_METRICS_TRIGGER_UPDATES]( [EventType] [nvarchar](50) NULL, [Parameters] [int] NULL, [EventInfo] [nvarchar](250) NULL ) ON [PRIMARY]
Step 2: Create a new Trigger CREATE TRIGGER [dbo].[tr_METRICS_upd_METRICS_All] on [dbo].[Metrics] for update as
--...trigger code...
Declare @SQL nvarchar(250) Set @SQL = 'DBCC InputBuffer(' + STR(@@SPID) + ')'
Insert into dbo.LOG_METRICS_TRIGGER_UPDATES Exec (@SQL)
Step 3: Query Logging Table
Select * from [dbo].[LOG_METRICS_TRIGGER_UPDATES] will show the EventInfo field containing DB Name and the outer object call.
|
|
|
|
|
|
|
|