Can't create MS SQL Server trigger

All we need is an easy explanation of the problem, so here it is.

I’m experiencing unexpected behaviour in my database.

I’m trying to create a trigger, and getting the error that it already exist:

Msg 2714, Level 16, State 2, Procedure xxxx_xxxxx_INSERT, Line 2
[Batch Start Line 0] There is already an object named
‘xxxx_xxxxx_INSERT’ in the database.

However, I don’t see a trigger with this name (below is the query I used to check), and I can’t find it via the management studio either.

select a.[name] as trgname, 
       b.[name] as [tbname] 
from sys.triggers a 
join sys.tables b on a.parent_id = b.object_id
order by trgname

What else can I check?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Namespaces are confusing and not well documented in SQL Server.

In your case, there will be another schema-scoped object in the database with the same identifier as the one you are hoping to use for your trigger.

My guess would be there is a stored procedure with the same name.

You can check schema-scoped objects in the sys.objects system view.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply