How can a trigger be bypassed?

Question

Assuming I have the following table:

Create Table [dbo].[Test_IP]
(
  [IP] varchar(40),
  [IPType] varchar(6)
)

There are more triggers on this table, but all of them are AFTER triggers, and it is large DB with many tables, views and stored procs, and it been used from several processes.

I added a log table and triggers like this

Create Table [dbo].[Log_Test_IP]
(
  [Action] varchar(10),
  [IP] varchar(40),
  [IPType] varchar(6)
)
GO
Create Trigger MYTR_Log_Test_IP_INS On [dbo].[Test_IP] After insert AS
BEGIN 
   insert into  [dbo].[Log_Test_IP]([Action],[IP],[IPType]) 
       select 'Insert', [IP], [IPType] from inserted 
END
GO
Create Trigger MYTR_Log_Test_IP_DEL On [dbo].[Test_IP] After delete AS
BEGIN
   insert into  [dbo].[Log_Test_IP]([Action],[IP],[IPType]) 
       select 'Delete', [IP], [IPType] from deleted  
END
GO

Now I'm getting to the interesting part: when I directly insert to the Test_IP table I can see the Insert trigger is working, but when it works regularly from an application or service (which I have no idea what it does) I don't see any Insert records in the log table, I can only see the 'Delete' records even though it was empty in the start.

My conclusion is that there is some way to bypass the triggers, there are many triggers and stored procedures in the DB and I have no idea where to look.

So, my question is how can you bypass the trigger?

Answer 1

Triggers will not run in a couple of scenarios.

1) Obviously if the trigger is disabled

2) If the trigger is marked as NOT FOR REPLICATION and the DML is issued by the replication Distribution Agent.

3) If the user has ALTER TABLE permissions and is performing a BULK INSERT, BCP or SqlBulkCopy, etc with the FIRE_TRIGGERS option disabled.

Answer 2

You cannot bypass a trigger, it will always fire based on the create criteria unless disabled. You can short-circuit the trigger by using temporary objects or context_info() but based on your description, this is not likely.

Your trigger should be firing but it isn't and that is slightly concerning. I'm thinking partition switching could even be at play. I'd stay away from profiler or traces as those are deprecated.

I would start by tracking the activity on the table, here is an extended event script written by Grant Fritchey that will capture all ADHOC queries on that table.

CREATE EVENT SESSION [QueryPerformance] ON SERVER
ADD EVENT sqlserver.sql_batch_completed (SET collect_batch_text = (1)
    ACTION (sqlserver.sql_text)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],
                                                    N'AdventureWorks2014')
           AND [sqlserver].[like_i_sql_unicode_string]([batch_text],
                                                       N'%Production.Document%')))
ADD TARGET package0.event_file (SET filename = N'QueryPerformance');

Source from SQL Central written by Grant Fritchey - The Scary DBA

I would also look to capture the events that update or insert the table in audit logs.

Nic of Stack Overflow wrote the code below:

USE [master]

GO

CREATE SERVER AUDIT [Audit-TblChanges]
TO FILE 
(   FILEPATH = N'C:\SQLAudit'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
)

GO

USE [YourDatabase]
GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-MyTable]
FOR SERVER AUDIT [Audit-TblChanges]
ADD (UPDATE ON OBJECT::[YourTable] BY [public]),
ADD (INSERT ON OBJECT::[YourTable] BY [public])

GO


USE [master]
GO
ALTER SERVER AUDIT [Audit-TblChanges] WITH (STATE = ON);

USE [YourDatabase]
GO
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-MyTable] WITH (STATE = ON);
GO

SQL Audit code source by Nic of Stack Overflow source.

As a last resort, I would look to reading through all of the stored procedures and look through anything that references that table.

Chains from stack overflow has a short snippet for you and more here for determining tables being used in stored procedures.

As always, test this in a dev environment and propagate to production with caution and proper QA.

Once again, thank you Grant, Chains, and Nic for your contributions!

Answer 3

You may try with SQL server profiler and see what is going from the app. The event SP:StmtCompleted should be added to trace