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