It used to be that the synonym (synonym) in SQL Server was useless, so it never looked up its syntax format. Today, there is a problem, with synonym to solve the better. The problem is that I use multiple databases in my system, and because some of the data in each database needs to be synchronized, I add triggers to the tables in one of those databases to synchronize updates to the data in other databases. Examples are as follows:
There are two databases A and B, with tables A1 and B1, and then I create a trigger in table A1 to update the data in the table B1, the trigger is probably as follows:
Create TRIGGER TU_A1 after update
As
......
Update T2 set t2. field =t1. field
From inserted t1,b. B1 T2 where T1. field =t2. field
Anyway, I used a B in the A1 trigger. This is the way to reference objects in database B.
And then the question came, and today the boss says to install multiple systems on the same server at the same time. Then you need to copy database A and database B multiple copies, of course, the database name can not be called A and B, may be called A1 and B1. The trigger in A1 cannot update the data in B1 because the trigger in A1 is using B. This way to refer to the. In this case, you must put all B.. All replaced by B1 ... In this way, if there are more places to cite, it is a disaster.
The use of synonyms synonym can solve this problem well, first of all about the role of synonyms (synonym).
synonym : Defines a string name that is used instead of a meaningful reference object.
In the example above, I can use the BB1 string to substitute B. B1, then I'm going to operate B. B1 This table, you can directly manipulate the BB1, with the same effect as the former. Then, when deploying multiple sets of systems, just modify the reference to this synonym in the new database, and the trigger does not need to be modified at all.
The following is an example of a SQL statement for "Create, delete, reference" synonyms, note that synonyms do not provide the functionality to modify, so once created, it can only be removed and rebuilt, and cannot be modified.
Create synonyms
Create synonym Bb1
From B.. B1
Using synonyms
INSERT into BBL (...) for values (...).
Reference synonyms
SELECT * FROM BBL
Delete synonyms
Drop synonym Bb1
Query all synonyms
SELECT * FROM sys.synonyms
Note: Synonym is only available in SqlServer2005 and later versions. Ong>.
Source: http://www.lmwlove.com/ac/ID1042
Usage of synonym synonym in SQL Server