Creating a multy Locale site in ColdFusion

 -- To create sites that would show date, time, currency and number as per the localization, it may be bit tricky. We may have to write logic for each nation separately. But it really not that easy. To ease this process, we can play a small trick in ColdFusion.
 

-- ColdFusion provides a function named ‘SetLocale()’.  This function sets the current session to some particular localization as per your request. And once you set the locale, now you can get the time, date, currency and number in that particular local format also you can verify whether some value is properly formatted or not.

-- To get all those above values we have some predefined methods in ColdFusion as: LSIsCurrency, LSCurrencyFormat, LSDateFormat, LSEuroCurrencyFormat, LSIsDate, LSParseDateTime, LSIsNumeric, LSNumberFormat, LSParseCurrency, LSParseEuroCurrency, LSParseNumber, LSTimeFormat.

-- This setting of localization stays as such for the current session only and once the session times out, the localization is set to default localization as per it’s set in ColdFusion server. This value can also be overridden using 'SetLocale()' function.

-- So the idea for creating a multiple localized site is: for each session, you can fetch the localization of the user in OnSessionStart method, may be using some API or having a database that would consist of user IPs and their localization names. Or simply we can have a dropdown in home page, where the user would pick the localization as per his/her choice.

-- Once you get the localization details, you supposed to have another look up table for localization and locale name. Available locale names that can be used in ColdFusion are:

Chinese (China)	French (Belgian)	Korean
Chinese (Hong Kong)	French (Canadian)	Norwegian (Bokmal)
Chinese (Taiwan)	French (Standard)	Norwegian (Nynorsk)
Dutch (Belgian)	French (Swiss)	Portuguese (Brazilian)
Dutch (Standard)	German (Austrian)	Portuguese (Standard)
English (Australian)	German (Standard)	Spanish (Modern)
English (Canadian)	German (Swiss)	Spanish (Standard)
English (New Zealand)	Italian (Standard)	Swedish
English (UK)	Italian (Swiss)
English (US)	Japanese

-- So once you get the locale name, now you can use SetLocale(locale_name) method to set the locale in OnSessionStart method. So this will be set just once and will stay effective for entire session. Hence I think this is the right place to set this.

-- Now as we have already set the locale, so by using above mentioned methods(Ls….), we would be able to deal with the locale specific formats easily.

Here is a Small example to show how to set locale and use time, date, currency and number as per the locale.

<cfoutput>
    <cfif StructKeyExists(form, 'localeText')>\
        <cfif Len(Trim(form.localeText))>\
            <cfset setLocale(form.localeText)>
        <cfelse>
            <b>Please select one language</b>
        </cfif>
    </cfif>

    <form method="post" action="">
        Enter your local:
        <select type="text" name="localeText" id="text" onchange="javascript: this.form.submit()">
            <option value="">-- Select Language --</option>
            <option value="Chinese (China)" >Chinese (China)</option>
            <option value="Chinese (Hong Kong)">Chinese (Hong Kong)</option>
            <option value="French (Belgian)">French (Belgian)</option>
            <option value="Dutch (Belgian)">Dutch (Belgian)</option>
            <option value="English (Australian)">English (Australian)</option>
            <option value="English (Canadian)">English (Canadian)</option>
            <option value="English (New Zealand)">English (New Zealand)</option>
            <option value="English (UK)">English (UK)</option>
            <option value="English (US)">English (US)</option>
            <option value="Japanese">Japanese</option>
            <option value="Swedish">Swedish</option>
        </select>
        <input type="submit" style="display: none;">
    </form>


    <p>
        The locale is now <b> #GetLocale()#</b><br />
        Your Date Now: #LsDateFormat(now())#<br />
        Your Time Now: #LsTimeFormat(now())#<br />
        Your Currency: #LsCurrencyFormat(350)#<br />
        Your Number View: #LsNumberFormat(350.12, '.__')#<br />
    </P>
</cfoutput>

Initial Form Look(Taking default US Locale):

So on choosing Chinese (China), the output would be:

So on choosing Japanese, the output would be:

So on choosing Swedish, the output would be:

SQL Triggers

What is a SQL Trigger?
Trigger is a special stored procedure which is executed upon firing of certain event. If you are aware of stored procedures, these are the block of compiled SQL statements being executed for some specific purpose.

So what is the difference between stored procedures and triggers?
Though both of them are coded in same way but while executing the same, there it makes a difference. Stored procedures can be called by your application code or by any other stored procedures, where as the triggers are only get called upon some specific database events as insert, update or delete.

Purpose of creating a Trigger
Triggers are generally created to maintain an audit table. The need of the audit table is, you want to keep track of different operations being carried out over a table. For this we need to create a separate table and can name it logically meaning audit table for some specific table. Ex. emp_audit

Types of Triggers
Triggers are basically of two types.
a. For/After Triggers
b. Instead Of Triggers

a. What is For/After Triggers
These are the triggers those execute after certain operation as insert/update/delete operation is done. Hence as per the operations the triggers are associated, they have been classified as that.

1. After Insert Trigger
2. After Update Trigger
3. After Delete Trigger

* Though we have grouped the triggers as above, but we can have a single trigger that will be executed after insert, update and delete operation.

Before I would explain about the trigger and their examples, let me first create a platform for that, that is let us create one dummy table and its audit table to be used in the example.

Suppose the table that we want to apply trigger, is "Employee". And the audit table for the same is 'Emp_audit'.

Employee table create statement :

CREATE TABLE Employee(

Id INT Identity,
EmpName VARCHAR(100),
EmpSalary DECIMAL (10,2)
)

Emp_audit table create statement:

CREATE TABLE Emp_audit(

Id INT,
EmpName VARCHAR(100),
EmpSalary DECIMAL (10,2),
ActionTaken VARCHAR(100),
AuditTimestamp DATETIME
) 

In the above mentioned audit table, we will keep the employee table record details along with the operation being made and the time stamp for the same.

1. After Insert Trigger
When we define a trigger as this, these are executed after the insert operation is done over the specified table(The table on which the trigger is written). So to keep track of the insertion being made, we can use this type of trigger and stored the inserted record details in some audit table.

Example:

CREATE TRIGGER afterInsertTrigger ON Employee 
FOR INSERT
AS
    DECLARE @id INT;
    DECLARE @empName VARCHAR(100);
    DECLARE @empSal DECIMAL(10,2);
    DECLARE @actionPerformed VARCHAR(100) = 'Employee record inserted.';
    DECLARE @currentDateTime DATETIME = getDate();

    -- Here inserted is the logical table which holds the last record being inserted
    SELECT @id = Id
        , @empname = EmpName
        , @empsal = EmpSalary
    FROM inserted;   

    -- Inserting the obtained data into emp_audit table
    INSERT INTO Emp_audit
    (
        Id
        , EmpName 
        , EmpSalary 
        , ActionTaken 
        , AuditTimestamp 
    ) 
    VALUES
    (
        @id
        , @empName
        , @empSal
        , @actionPerformed
        , @currentDateTime
    );

    PRINT 'One record inserted into employee table.'

 Explanation:

Here we have got the inserted record details from 'inserted'(a logical table) table that keeps track of the last inserted record details. Then we used the retrieved data to insert into the emp_audit table.

2. After Update Trigger
These type of triggers are used to keep track of the update operation on tables. Please find the example below for having detailed idea on the same.

Example:

CREATE TRIGGER afterUpdateTrigger ON Employee 

FOR UPDATE

AS

    DECLARE @id INT;
    DECLARE @empName VARCHAR(100);
    DECLARE @empSal DECIMAL(10,2);
    DECLARE @actionPerformed VARCHAR(100);
    DECLARE @currentDateTime DATETIME = getDate();

     -- Here inserted is the logical table which holds the last record being inserted
    SELECT @id = Id
        , @empname = EmpName
        , @empsal = EmpSalary
    FROM inserted;   

    if update(EmpName)
        set @actionPerformed ='Name modified.';
    if update(EmpSalary)
        set @actionPerformed ='Salary modified.';

    -- Inserting the obtained data into emp_audit table
    INSERT INTO Emp_audit

    (
        Id
        , EmpName 
        , EmpSalary 
        , ActionTaken 
        , AuditTimestamp 
    ) 

    VALUES
    (
        @id
        , @empName
        , @empSal
        , @actionPerformed
        , @currentDateTime
    );
    PRINT 'One record updated in employee table.'

Explanation:

Here if you compare the after update trigger with after insert trigger, there is not a much difference
between both. Both use inserted logical table to get the inserted or updated record details. The only difference is you can detect the column being updated using 'update(column_name)' method. This method speaks about whether a column has been modified or not.

 3. After Delete Trigger

This trigger helps in keep track of the records being deleted. Here the example would be just the same as above insert and update trigger, but here we will get details about the deleted records from deleted logical table instead of inserted.

b. Instead Of Triggers
This type of trigger is used as a checkpoint for database operations. i.e. while you would go for database operation, before it actually committing the changes, it goes for execution of this trigger and here in this trigger we can have conditional check to see whether to commit the changes or rollback the same. Please see below example for a clear idea on this.

Example:

CREATE TRIGGER insteadOfDeleteTrigger ON Employee 
INSTEAD OF DELETE
AS
    DECLARE @id INT;
    DECLARE @empName VARCHAR(100);
    DECLARE @empSal DECIMAL(10,2);
    DECLARE @actionPerformed VARCHAR(100) = 'One employee record deleted';
    DECLARE @currentDateTime DATETIME = getDate();
    
    -- Getting the deleted record details from 'deleted' logical table
    SELECT @id = Id
        , @empname = EmpName
        , @empsal = EmpSalary
    FROM deleted;

    BEGIN
    
        -- Here under some condition either we are making rollback of the changes or we are committing the same
        if(@empsal < 1000)
            begin
                RAISERROR('Employees with salary less than 1000 can not be deleted',16,1);
                ROLLBACK;
            end
        else
            begin
                DELETE FROM Employee 
                    WHERE Id = @id;
                COMMIT;
                INSERT INTO Emp_audit
                (
                    Id
                    , EmpName 
                    , EmpSalary 
                    , ActionTaken 
                    , AuditTimestamp 
                ) 
                VALUES
                (
                    @id
                    , @empName
                    , @empSal
                    , @actionPerformed
                    , @currentDateTime
                );

                PRINT 'One record is deleted from employee table.'
            end
    END

Explanation:

As you can see in above example, during deleting any record, it first checks if the record satisfies certain condition. If only it satisfies the condition, then only we will commit the changes else we will rollback it.

My First Blog Post

After waiting for a long days, finally I thought of having my own blog. Though I have written tips and articles on ColdFusion, helped friends and colleagues in understanding concepts with ColdFusion, but have not thought of seriously of having my own blog to publish my ideas, thoughts and concepts related to ColdFusion, jQuery, CSS and SQLServer.

I believe I will be able to share my experience with ColdFusion and other client side languages and Data base with other interested people around.