CODEWITHSUNDEEP
sqlsql-servertriggerssql-server-2008-r2
eversor
eversor

Reputation: 3073

Lost trigger in SQL Server

I have lost a trigger. I know it is active, since when I try to change a field, it restores its value back, and from the SQL Server Management Studio I can see that when I execute the query, an extra one is executing (the trigger one).

I tried SELECT * FROM sys.triggers WHERE 1 = 1 but it does not return anything. Also, I know the name of the trigger, but when I try to ALTERit returns an error saying that

the name of the object 'myTrigger' is not valid.

And if I try to DROP TRIGGER myTrigger ON DATABASE

Trigger 'myTrigger' can not be removed because it does not exists, or the user has not enough priviledges.

Upvotes: 2

Views: 2025

Answers (2)

Seán Gahan
Seán Gahan

Reputation: 84

I had a subtle error kind of like yours; I did something like this:

CREATE TRIGGER [TrigCustomerInsertDelete] 

ON [mobile].[Customers] 

AFTER INSERT, DELETE   
AS  

    select 'I'  [ACTION],* from inserted
    select 'D'  [ACTION],* from deleted

GO

and then later tried to update it with:

ALTER TRIGGER [TrigCustomerInsertDelete]

and got this error: Invalid object name

The problem was that I did not specify the schema on the trigger (thinking it would just be dbo), but the table does have a schema. So, I figured out the error after including the schema on the trigger.

CREATE TRIGGER [DBO].[TrigCustomerInsertDelete]

This gave me the following error: Cannot create trigger 'DBO.TrigCustomerInsertDelete' because its schema is different from the schema of the target table or view.

I know this is an old post, but hopefully it will help someone else.

Upvotes: 0

eversor
eversor

Reputation: 3073

Ok, as Lieven said, I was not querying the right database... This is odd, since the query had the DB defined on it 

ALTER TRIGGER myTrigger
ON [DB].[dbo].[table]

But the upper select of the SQL Server Management Studio was pointing to other DB...

Edit: I have been investigating a little bit (trying to clean my image), and I found a weird behaviour on SQL Server Management Studio. If you execute a query like:

UPDATE [DB].[dbo].[table] SET column = 'value' WHERE column2 = 'foo'

It executes the query in the right DB. No matter what DB is pointed by the upper select.

But, if you try to do the same, with a trigger, you must have the upper select pointing the right DB, or you will face the same problems I had.

Upvotes: 2

Related Questions