Help Needed With INSERT Trigger

Hello,
It is rare that I have a user for Triggers, but I have a special case. In a table named tbl_PaymhistCurrentWeek, I have a column called 'AcctCode'. This column will contain data that will be derived from another column called AcctCodeWithZeros. AcctCodeWithZeros contains an account code with leading zeros. The column AcctCode needs to contain the value in AcctCodeWithZeros, only without the leading zeros.

I tried to set up something in a Derived Column Transformation within SSIS, but I can't use PATINDEX.

Here is the latest version of the Trigger:

*****************
CREATE TRIGGER [dbo].[tr_AcctCode]
ON [dbo].[tbl_PaymhistCurrentWeek]
FOR INSERT
AS

BEGIN
INSERT tbl_PaymhistCurrentWeek
(AcctCode, Co, AccountNoWithZeros)
SELECT
id.SUBSTRING([AccountNoWithZeros], PATINDEX('%[^0]%', [AccountNoWithZeros]), 14)AS AcctCode, id.Co, id.AccountNoWithZeros
FROM Inserted id
END
****************
The default for AcctCode is an empty string, which is all I get when I insert into the table. AcctCode will be part of a primary key, so I don't think I can set up AcctCode to be a calculated column. What am I doing wrong?

The DDL for the table is as follows:
tbl_PaymhistCurrentWeek
( AcctCode varchar(10) Default ' ' Not Null,
Co char(2) Not Null,
AcctCodeWithZeros varchar(20) Null
CONSTRAINT (PK_PaymhistCurrentWeek) Primary Key Clustered
(AcctCode, Co)
)


Thank you for your help!

cdun2