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.
|
|
|
|
|
|
|
|
|
|