Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

The title says (asks) it all. Is it possible to create a trigger on a table synonym in SQL Server (2005 or higher)?

share|improve this question

closed as off topic by Paul White, Phil, dezso, Mark Storey-Smith, RolandoMySQLDBA Jan 30 '13 at 4:22

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

What would you hope to accomplish with creating a trigger against a synonym? Random what-if question or is there a business problem you hoped to solve with it? – billinkc Jan 29 '13 at 15:56
@billinkc: Random? Not at all. I have to test an application that creates several triggers on several tables and I made the very naive assumption that if it was possible, I could remove them before each test run by simply dropping the sysonyms (thus dropping the triggers on them as well) without affecting the original triggers on original tables. Now that I know it's not possible, I'll stop being lazy and create a proper removal script. – gemisigo Jan 30 '13 at 8:20
up vote 7 down vote accepted

No, it is not. I assume you tried this, but you will get this error:

Msg 8197, Level 16, State 6, Procedure EasyToTest, Line 1
The object 'dbo.ShowSomeEffort' does not exist or is invalid for this operation.

You also can't create a constraint on a synonym, reference a synonym in a foreign key constraint, etc. Most metadata operations must apply to the base table, not the synonym. The synonym is essentially used as a "syntactic sugar" pointer to assist in DML/CRUD, not DDL.

share|improve this answer
No, I haven't. By the time I had the chance to try I already got the answer it was not possible, thank you very much. – gemisigo Jan 30 '13 at 8:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.