October 31, 2007 at 3:26 pm
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?
Thank you for your help!
CSDunn
October 31, 2007 at 3:42 pm
Can you post the DDL of the table ?
* Noel
November 1, 2007 at 7:36 am
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)
)
November 1, 2007 at 9:14 am
CREATE TABLE tbl_PaymhistCurrentWeek
( AcctCode AS ISNULL(SUBSTRING(AcctCodeWithZeros, PATINDEX('%[^0]%', AcctCodeWithZeros), 14),''),
Co char(2) Not Null,
AcctCodeWithZeros varchar(20) Null,
CONSTRAINT PK_PaymhistCurrentWeek Primary Key Clustered
(AcctCode, Co)
)
Insert into tbl_PaymhistCurrentWeek (Co, AcctCodeWithZeros) VALUES ('C1','0000123')
Insert into tbl_PaymhistCurrentWeek (Co, AcctCodeWithZeros) VALUES ('C2','0001234')
Insert into tbl_PaymhistCurrentWeek (Co, AcctCodeWithZeros) VALUES ('C3','0012345')
Insert into tbl_PaymhistCurrentWeek (Co, AcctCodeWithZeros) VALUES ('C1','0000123') -- Fail
Insert into tbl_PaymhistCurrentWeek (Co, AcctCodeWithZeros) VALUES ('C1',NULL) -- Defaults to ''
select * from tbl_PaymhistCurrentWeek
* Noel
November 1, 2007 at 1:39 pm
Thank you for your help!
CSDunn
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply