April 1, 2022 at 3:26 am
Hi , We have all Varchar type columns in a table. We have a new requirement which needs to have both varchar columns as well as new column with the collation to support Māori language. So, I decided to create a new Nvarchar column . But now the problem is user will enter the data from the front end for one of the column with Māori character, from trigger, I need to update other column without the Māori characters.
What I tried is,
CREATE TABLE [dbo].[Collationtest](
[Title] [nvarchar](60) NULL,
[TitleNormal] [nvarchar](60) NULL,
[ID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Declare @var Nvarchar(60)
set @Var = N'kaka means clothes, kakā means spicy, and kākā means parrot.'
INSERT INTO [dbo].[Collationtest]([Title]) values(@Var)
Trigger
ALTER TRIGGER [dbo].[trg_Collationtest]
ON [dbo].[Collationtest]
AFTER INSERT,UPDATE
AS
BEGIN
DECLARE @ID INT
DECLARE @Title NVarchar(60)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
SELECT @ID = ID, @TitleDesc = Title FROM inserted
Update Collationtest SET TitleNormal = Substring(@Title,1,LEN(@Title)) WHERE ID = @ID
END
But, both columns in the backend showing as below.
kaka means clothes, kakā means spicy, and kākā means parrot.
How can I achieve the update of the TitleNormal column without unicode characters when I insert data with unicode characters in Title column?
Any help would be appreciated.
April 1, 2022 at 8:22 am
Your trigger will fail if more than one row is inserted or updated in a batch, so I suggest that you fix that.
What do you want TitleNormal to be updated to in the case of your example? You say 'without unicode characters', which possibly implies this
kaka means clothes, kak means spicy, and kk means parrot.
Is that your requirement?
April 4, 2022 at 6:10 am
I worked the above with Cast to varchar when updating the TitleNormal.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply