Search code examples
sql-servert-sqltriggers

Create or alter trigger in other schema: There is already an object named 'myTrigger' in the database

I'm stuck with a weird problem with my new schema and can't find a solution. I successfully created a trigger using following statement:

CREATE OR ALTER TRIGGER myTrigger
ON [mySchema].[myTable]
AFTER INSERT
AS
BEGIN
    -- do something
END

But now, everytime I reuse the above statement, I get the error:

There is already an object named 'myTrigger' in the database.

When I remove the CREATE OR and only use ALTER TRIGGER myTrigger, I get the error:

Invalid object name 'myTrigger'.

I tried using a different name, same behavior. I have another trigger in the dbo schema where it works totally fine. I even tried using the same code as in the dbo trigger and got the same error messages. So there must be something wrong with the schema although I gave it the same permissions as the dbo schema. What am I missing here?

Solution

  • As @siggemannen suggested in the comments, it works by specifying the schema in the statement:

    CREATE OR ALTER TRIGGER mySchema.myTrigger
ON mySchema.myTable

    While the trigger did get created correctly in the mySchema schema even without specification, it couldn't be accessed by the ALTER statement without explicitly specifying it.