0

I created a database trigger last week in our QA environment to track schema and database changes. I am trying to remove the tables, and I have removed all the triggers but when I try to drop the last table I get the following error

Msg 208, Level 16, State 1, Procedure trg_Database_changes, Line 8 [Batch Start Line 2]
Invalid object name 'Audit_logs'

This makes me think I still have a trigger out there but I can't find it. I have looked in every database.

Any ideas?

USE [master]
GO

IF EXISTS (SELECT * FROM sys.objects 
           WHERE object_id = OBJECT_ID(N'[dbo].[Audit_logs]') AND type in (N'U'))
    DROP TABLE master.[dbo].[Audit_logs]
GO
Dale K
  • 25,246
  • 15
  • 42
  • 71
Pam Kagel
  • 1
  • 1
  • Hi, have you try to list all triggers ? i think this query can help you https://www.sqlservertutorial.net/sql-server-triggers/sql-server-list-all-triggers/ https://stackoverflow.com/questions/4305691/need-to-list-all-triggers-in-sql-server-database-with-table-name-and-tables-sch – Sanpas Mar 08 '21 at 21:05
  • I have. That's how I checked to see if it existed somewhere I didn't know. – Pam Kagel Mar 08 '21 at 21:11
  • 1
    for sure you have a trigger on your database that tracks database changes & probably it is using the same table "Audit_logs" , that's why its failing – eshirvana Mar 08 '21 at 21:16
  • I think you have procedure (stored procedure) and this SP use Audit_Logs table – Sanpas Mar 08 '21 at 21:23
  • So you have no triggers in `sys.triggers` in each user database, nor any triggers in `sys.server_triggers` (where `all server` triggers are listed)? – AlwaysLearning Mar 08 '21 at 21:47
  • DDL triggers cannot be seen in sys.objects. Try sys.triggers - which has both DML and DDL triggers. – SMor Mar 08 '21 at 22:16
  • I found the issue. It was a server level trigger. Thank you all for your help. Those don't show up in sys.triggers either. – Pam Kagel Mar 09 '21 at 21:21

0 Answers0