Prev: SMO
Next: Replace all alpabet characters
From: littleccguy on 30 Jun 2008 10:17
I am a little novice at running queries, so please be patient with me.

Windows Server 2000
SQL Server 2005

I am trying this is a test database first.
query:
UPDATE VEND_addr
SET COUNTRY_CD = 'USA',
SALES_TAX_CD = 'CA',
SHIP_ID = NULL,
EMAIL_ID = NULL,
IBAN_CD = NULL,
EDI_N104_CD = NULL,
NON_US_BANK_ACCT_ID = NULL
where vend_id = '2602'

error:
Msg 40005, Level 16, State 1, Procedure VEND_ADDR_UPDTRIG, Line 95
not in SALES_TAX when changing child VEND_ADDR

Thanks
From: Matthew Bando on 30 Jun 2008 10:30
It looks like your error is occurring in an update trigger defined on this
table. Maybe there is code there which is encountering an error. You can
find the trigger code using the SSMS and expanding the Triggers folder under
that particular table (vend_addr).

Look at the code defined for the trigger and see if maybe you are missing
some required data.

Thanks,

Matt

"littleccguy" wrote:

> I am a little novice at running queries, so please be patient with me.
>
> Windows Server 2000
> SQL Server 2005
>
> I am trying this is a test database first.
> query:
> UPDATE VEND_addr
> SET COUNTRY_CD = 'USA',
> SALES_TAX_CD = 'CA',
> SHIP_ID = NULL,
> EMAIL_ID = NULL,
> IBAN_CD = NULL,
> EDI_N104_CD = NULL,
> NON_US_BANK_ACCT_ID = NULL
> where vend_id = '2602'
>
> error:
> Msg 40005, Level 16, State 1, Procedure VEND_ADDR_UPDTRIG, Line 95
> not in SALES_TAX when changing child VEND_ADDR
>
> Thanks
>
From: littleccguy on 30 Jun 2008 10:46
You are right. There is a trigger there. Can you help me understand
what is happening here. Here is an excerpt:

if update(COUNTRY_CD) or update(MAIL_STATE_DC)
BEGIN
/* F05140 update change restrict */
IF (SELECT COUNT(*) FROM DELTEK.MAIL_STATE, inserted where
DELTEK.MAIL_STATE.COUNTRY_CD = inserted.COUNTRY_CD AND
DELTEK.MAIL_STATE.MAIL_STATE_DC = inserted.MAIL_STATE_DC) !=
(@num_rows - (select count(*) from inserted where
inserted.COUNTRY_CD is null OR
inserted.MAIL_STATE_DC is null ))
BEGIN
RAISERROR 40005 'not in MAIL_STATE when changing child
VEND_ADDR'
ROLLBACK TRAN RETURN
END
END
if update(SALES_TAX_CD) or update(COMPANY_ID)
BEGIN
/* F05290 update change restrict */
IF (SELECT COUNT(*) FROM DELTEK.SALES_TAX, inserted where
DELTEK.SALES_TAX.SALES_TAX_CD = inserted.SALES_TAX_CD AND
DELTEK.SALES_TAX.COMPANY_ID = inserted.COMPANY_ID) !=
(@num_rows - (select count(*) from inserted where
inserted.SALES_TAX_CD is null ))
BEGIN
RAISERROR 40005 'not in SALES_TAX when changing child
VEND_ADDR'
ROLLBACK TRAN RETURN
END
END

Also an FYI: We inserted the new rows Thursday into the test and live
successfully. Of course now the users are coming backing saying
"sorry, i gave you the wrong info." huh, go figure. ;)

I am trying to find out from the users if they entered any
transactions in regards to the new vendors. If they haven't, i should
be able to successfully do a delete and reinsert, but if not, i need
to run the update.
From: littleccguy on 30 Jun 2008 11:32
Thanks!!! I am working my way through it now. I have it down to one
column I cannot update until the corresponding table has the
appropriate entry.

Thanks again for pointing me in the right direction.
 | 
Pages: 1
Prev: SMO
Next: Replace all alpabet characters