Chinese in Canada

Have a sample table as:
USE [WlkDB]
GO
/****** Object: Table [dbo].[Dict] Script Date: 05/05/2009 10:13:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Dict](
[idxKey] [int] IDENTITY(1,1) NOT NULL,
[Cat_ID] [nchar](10) NOT NULL,
[KEY_Code] [nchar](10) NOT NULL,
[KEY_Name] [nchar](60) NULL,
[KEY_LNK] [nchar](50) NULL,
[DT_Create] [datetime] NULL CONSTRAINT [DF_Dict_DT_Create] DEFAULT (getdate()),
[DT_Modify] [datetime] NULL,
[Operator] [nchar](50) NULL,
[Status] [int] NULL CONSTRAINT [DF_Dict_Status] DEFAULT ((-1)),
[Notes] [nchar](100) NULL,
CONSTRAINT [PK_Dict] PRIMARY KEY CLUSTERED
(
[idxKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_Dict] UNIQUE NONCLUSTERED
(
[idxKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

create the trigger:
USE [WlkDB]
GO
/****** Object: Trigger [dbo].[CheckDictionary] Script Date: 05/05/2009 10:16:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: John Zhang
-- Create date: March 26, 2009
-- Description: Updated the modifing time of dict automatically.
-- and keep the last update information inside the notes field.
-- =============================================
CREATE TRIGGER [dbo].[CheckDictionary]
ON [dbo].[Dict]
AFTER UPDATE
AS
/* If you must test for updates that affect columns other than the first 8 columns in a table,
you must use the SUBSTRING function to test the proper bit returned by COLUMNS_UPDATED.
This example tests for updates that affect columns 3, 5, or 9 in the table. */
PRINT COLUMNS_UPDATED()
IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))
+ power(2,(5-1)))
AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
)
BEGIN
PRINT 'Columns 3, 5 and 9 updated'
DECLARE @ST_old int, @ST_new int,@pos int;
SELECT @pos=del.idxkey,@ST_old = del.Status FROM deleted del;
SELECT @ST_new = ins.Status FROM inserted ins;
UPDATE [dbo].[Dict]
SET Notes = rtrim(Notes)+N':N:'+RTRIM(CAST(@ST_old as nchar))+N'...'+CAST(@ST_new as nchar)
WHERE idxkey=@pos
END
/*Check if any of 3,5,9 changed, update the modify time*/
IF (COLUMNS_UPDATED() & 5121)>0
BEGIN
PRINT 'Column 3, 5 or 9 updated'
DECLARE @pCode nvarchar(2), @nCode nvarchar(2),@Status_old int, @Status_new int,@pos1 int;
SELECT @pos1=del.idxkey,@pCode = del.KEY_Code,@Status_old = del.Status FROM deleted del;
SELECT @Status_new = ins.Status, @nCode = ins.KEY_Code FROM inserted ins;
UPDATE [dbo].[Dict]
SET Notes = N'C:'+rtrim(@pCode)+'->'+rtrim(@nCode)+' St:'+RTRIM(CAST(@Status_old as nchar))+N'->'+CAST(@Status_new as nchar)
,DT_Modify = GetDate()
WHERE idxkey=@pos1
END
/*Check whether columns 2, 3 or 4 has been updated.
If any or all of columns 2, 3 or 4 have been changed, create an audit record.
The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14.
To check if all columns 2, 3, and 4 are updated, use = 14 in place of >0 (below).*/
-- IF (COLUMNS_UPDATED() & 14) > 0
/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3, and 4 are updated.*/
IF (COLUMNS_UPDATED() & 2048) > 0
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Name_old nchar(60), @Name_new nchar(60),@pos2 int;
SELECT @pos2=del.idxkey,@Name_old = del.KEY_Name FROM deleted del;
SELECT @Name_new = ins.KEY_Name FROM inserted ins;

UPDATE [dbo].[Dict]
SET Notes = rtrim(Notes)+N'N:'+rtrim(@Name_old)+N'...'+rtrim(@Name_new)
,DT_Modify = GetDate()
WHERE idxkey=@pos2

-- Insert statements for trigger here

END