Using triggers and views to implement multiple-language output of a table

Without the standardization process involved, it is very difficult for programmers to develop an application that can be used in a variety of languages. If possible, the translation process can be done in the database, so that developers have a lot of convenience, so that they can focus on the development of their own things.

For example, suppose we now have a list of countries. The name of the country varies according to the language of the end user. In the absence of translators at the time, we need to export the country name according to the language that the user sets, or use the default language-English for output.

To do this, we first create a table that stores information about these countries in our base language, which is created as follows:

CREATE TABLE Base_countries
(
A2 char (2) NOT NULL,
A3 char (3) NOT NULL,
Num CHAR (3) NOT NULL,
Name VARCHAR2 NOT NULL,
Constraint BASE_COUNTRIES_PK primary KEY (A2)
);

Now, we have created a transformation table. Typically, this conversion form requires only three elements: the main code of the table, the language code, and the translation. If more than one column needs to be transformed, we can also add an identifier to record the current position. This also requires us to make a reference to the underlying table, referring to the following code:

CREATE TABLE Tran_countries
(
A2 char (2) NOT NULL,
Lang varchar2 (2) NOT NULL,
Name VARCHAR2 NOT NULL,
Constraint TRAN_COUNTRIES_PK primary KEY (A2,lang),
Constraint TRAN_COUNTRIES_FK foreign KEY (A2) references base_countries (A2)
);

Then, we can create a view on the underlying table and transformation table using the currently specified language code, which is set in Userenv (' LANG ') and created as follows:

Create or replace view nls_countries as
Select Base_countries.a2,
BASE_COUNTRIES.A3,
Base_countries.num,
NVL (tran_countries.name,base_countries.name) name
From Base_countries,tran_countries
where base_countries.a2 = TRAN_COUNTRIES.A2 (+)
and Userenv (' LANG ') = Tran_countries.lang (+);

Alternatively, we can create a view with oracle9i outer joins and syntax, as follows:

Create or replace view nls_countries as
Select Base_countries.a2,
BASE_COUNTRIES.A3,
Base_countries.num,
NVL (tran_countries.name,base_countries.name) name
From base_countries left outer join tran_countries
On base_countries.a2 = Tran_countries.a2
and Tran_countries.lang = Userenv (' lang ');

Because this view is created from an external connection and does not get the same primary key as its base table, it does not have a protection key. This means that we must create a "instead-of" trigger to complete the INSERT/Update/delete action on the view. For the example we have mentioned above, we can assume that if the current user locale is English, they will operate on the underlying tables, otherwise they will operate on the conversion table. Therefore, in practical use, you need to check whether there are non-English users in the underlying table to update or raise objections. So how do you design this trigger? You can refer to the following methods:

Create or Replace Trigger NLS_COUNTRIES_INSTRG
Instead of insert on nls_countries
For each row
Begin
If Userenv (' LANG ') in (' US ', ' GB ') then
INSERT into base_countries values (: new.a2,:new.a3,:new.num,:new.name);
Else
---This would fail if base_countries doesn ' t have matching row
INSERT into tran_countries values (: new.a2,userenv (' LANG '),: New.name);
End If;
End
/
Show errors;

Create or Replace Trigger NLS_COUNTRIES_UPDTRG
Instead of update on nls_countries
For each row
Begin
If Userenv (' LANG ') in (' US ', ' GB ') then
Update Base_countries
Set a2 =: new.a2,
A3 =: new.a3,
num =: New.num,
Name =: New.name
WHERE a2 =: old.a2;
Else
Update Tran_countries
Set a2 =: new.a2,
Name =: New.name
WHERE a2 =: old.a2
and Lang = Userenv (' lang ');
End If;
End
/
Show errors;

Create or Replace Trigger NLS_COUNTRIES_DELTRG
Instead of delete on nls_countries
For each row
Begin
If Userenv (' LANG ') in (' US ', ' GB ') then
Delete from base_countries where a2 =: old.a2;
Else
Delete from Tran_countries
WHERE a2 =: old.a2 and lang = Userenv (' lang ');
End If;
End
/
Show errors;

Here we give some demonstration data, and we take only those countries whose names begin with the letter "A":

INSERT into base_countries values (' AF ', ' AFG ', ' 004 ', ' Afghanistan ');
INSERT into base_countries values (' AL ', ' ALB ', ' 008 ', ' Albania ');
INSERT into base_countries values (' DZ ', ' dza ', ' 012 ', ' Algeria ');
INSERT into base_countries values (' as ', ' ASM ', ' 016 ', ' American Samoa ');
INSERT into base_countries values (' AD ', ' and ', ' 020 ', ' Andorra ');
INSERT into base_countries values (' AO ', ' AGO ', ' 024 ', ' Angola ');
INSERT into base_countries values (' AI ', ' AIA ', ' 660 ', ' Anguilla ');
INSERT into base_countries values (' AQ ', ' ATA ', ' 010 ', ' Antarctica ');
INSERT into base_countries values (' AG ', ' ATG ', ' 028 ', ' Antigua and Barbuda ');
INSERT into base_countries values (' AR ', ' ARG ', ' 032 ', ' Argentina ');
INSERT into base_countries values (' AM ', ' ARM ', ' 051 ', ' Armenia ');
INSERT into base_countries values (' AW ', ' ABW ', ' the ', ' Aruba ');
INSERT into base_countries values (' AU ', ' AUS ', ' 036 ', ' Australia ');
INSERT into base_countries values (' at ', ' AUT ', ' 040 ', ' Austria ');
INSERT into base_countries values (' AZ ', ' aze ', ' 031 ', ' Azerbaijan ');

INSERT into tran_countries values (' AF ', ' E ', ' Afghanistan ');
INSERT into tran_countries values (' AL ', ' CS ', ' Albanie ');
INSERT into tran_countries values (' AL ', ' D ', ' Albanien ');
INSERT into tran_countries values (' AL ', ' F ', ' Albanie ');
INSERT into tran_countries values (' DZ ', ' CS ', ' Alzir ');
INSERT into tran_countries values (' DZ ', ' D ', ' Algerien ');
INSERT into tran_countries values (' DZ ', ' E ', ' Argelia ');
INSERT into tran_countries values (' DZ ', ' F ', ' Algerie ');
INSERT into tran_countries values (' as ', ' CS ', ' Americka Samoa ');
INSERT into tran_countries values (' as ', ' D ', ' Amerikanisch-samoa ');
INSERT into tran_countries values (' as ', ' E ', ' Samoa Americanes ');
INSERT into tran_countries values (' as ', ' F ', ' Samoa Americana ');
INSERT into tran_countries values (' AD ', ' F ', ' Andorre ');
INSERT into tran_countries values (' AQ ', ' CS ', ' antarktis ');
INSERT into tran_countries values (' AQ ', ' D ', ' antarktis ');
INSERT into tran_countries values (' AQ ', ' F ', ' Antarctique ');
INSERT into tran_countries values (' AG ', ' CS ', ' Antigua a Barbuda ');
INSERT into tran_countries values (' AG ', ' D ', ' Antigua und Barbuda ');
INSERT into tran_countries values (' AG ', ' E ', ' Antigua y Barbuda ');
INSERT into tran_countries values (' AG ', ' F ', ' Antigua-et-barbuda ');
INSERT into tran_countries values (' AR ', ' D ', ' Argentinien ');
INSERT into tran_countries values (' AR ', ' F ', ' Argentine ');
INSERT into tran_countries values (' AM ', ' CS ', ' Armanie ');
INSERT into tran_countries values (' AM ', ' D ', ' Armenien ');
INSERT into tran_countries values (' AM ', ' F ', ' Armenie ');
INSERT into tran_countries values (' AU ', ' CS ', ' Australie ');
INSERT into tran_countries values (' AU ', ' D ', ' australien ');
INSERT into tran_countries values (' AU ', ' F ', ' Australie ');
INSERT into tran_countries values (' at ', ' CS ', ' Rakousko ');
INSERT into tran_countries values (' at ', ' D ', ' Sterreich ');
INSERT into tran_countries values (' at ', ' F ', ' Austriche ');
INSERT into tran_countries values (' AZ ', ' CS ', ' Azerbajdzan ');
INSERT into tran_countries values (' AZ ', ' D ', ' Aserbaidschan ');
INSERT into tran_countries values (' AZ ', ' E ', ' Azerbaijan ');
INSERT into tran_countries values (' AZ ', ' F ', ' Azerba?djan ');
Commit
User language to query the view:

Sql> select * from Nls_countries;

A2 A3 NUM NAME
-- --- --- ----------------------
AF AFG 004 Afghanistan
AL ALB 008 Albania
DZ Dza 012 Algeria
As ASM 016 American Samoa
AD and 020 Andorra
AO AGO 024 Angola
AI AIA 660 Anguilla
AQ ATA 010 Antarctica
AG ATG 028 Antigua and Barbuda
AR ARG 032 Argentina
AM ARM 051 Armenia
AW ABW Aruba
AU AUS 036 Australia
At AUT 040 Austria
AZ Aze 031 Azerbaijan

Rows selected.

Sql> alter session Set nls_language = ' FRENCH ';

Session MODIFIEE.

Sql> select * from Nls_countries;

A2 A3 NUM NAME
-- --- --- ------------------------------------
AF AFG 004 Afghanistan
AL ALB 008 Albanie
DZ Dza 012 Algerie
As ASM 016 Samoa Americana
AD and 020 Andorre
AO AGO 024 Angola
AI AIA 660 Anguilla
AQ ATA 010 Antarctique
AG ATG 028 Antigua-et-barbuda
AR ARG 032 Argentine
AM ARM 051 Armenie
AW ABW Aruba
AU AUS 036 Australie
At AUT 040 Austriche
AZ Aze 031 Azerba?djan

Ligne (s) selectionnee (s).
We can also use Sql*loader to take the converted data from a text file and store it in the database, Sql*loader actually calls the delete (belongs to replace) and insert these two triggers. If we set the Nls_lang parameter before running Sql*loader, the inserted data is automatically inserted into the conversion table with the currently set language code. As the following example:

Load Data characterset WE8ISO8859P1
InFile *
Replace
into table nls_countries
Fields terminated by ', ' optionally enclosed by ' '
(A2,name)
Begindata
Al, "Albanie."
DZ, "Algerie"
As, "Samoa Americana"
AD, "Andorre"
AQ, "Antarctique."
AG, "Antigua-et-barbuda"
AR, "Argentine"
AM, "Armenie"
AU, "Australie"
At, "Austriche"
AZ, "Azerba?djan"

Set Nls_lang=france_french. We8iso8859p1
Sqlldr Userid=scott/tiger Control=nlsview.ctl

Sql*loader:release 8.1.7.0.0-production on Di Mar 30 20:26:40 2003
(c) Copyright to Oracle Corporation. All rights reserved.
Point de validation (COMMIT) Atteint-nombre d ' Enregis. Logiques 11