Hi!
> if @lctype = "R"
Should be
> if @lctype = 'R'
"" used to wrap the field name with spaces, like "My Long Long Field Name". The same for table names. Character constants should be enclosed into the single quotes ('').
HTH.
>This is more of sql server question. I hope it is ok in this forum.
>I am attempting to modify the structure of a sql server table in enterprise manager. Whenever i attempt to add a field, any field, and click save, I get the message:
>
>'Unable to preserve trigger arinvc_update. Invalid column name 'R'
>
>I don't have a column named 'R'.
>
>This is an existing database, with existing stored procedures and triggers (I didn't write them) that I need to maintain/update.
>
>The stored procedure in question is as follows:
>
>/** ID: AR50048.01 Name: arinvc_update Owner: AM ScriptDate: 03/20/2000 **/
>create trigger arinvc_update ON dbo.arinvc
>for update
>as
>begin
> if exists (select lexcl from cosyst where lexcl = 1) /* exclusive mode */
> return
> declare @lcuid char(15), @lnsalesamt numeric(16, 2), @lndiscamt numeric(16, 2),
>
> @lnbalance numeric(16, 2), @lctype char(1), @lnvoid smallint, @lcinvcuid char(15),
> @lcpaycode char(10), @lccardno char(20), @lcexpdate char(5), @lccardname char(30),
> @lccustno char(10), @lnatdsamt numeric(16, 4), @lcinvno char(10), @lnpaytype integer
> declare curarinvc_upd scroll cursor for select nsalesamt, ndiscamt, nbalance,
> ctype, lvoid, cuid, ccardno, cpaycode, cexpdate, ccardname, ccustno, cinvno
> from inserted
> open curarinvc_upd
> fetch next from curarinvc_upd into @lnsalesamt, @lndiscamt, @lnbalance,
> @lctype, @lnvoid, @lcinvcuid, @lccardno, @lcpaycode, @lcexpdate, @lccardname,
> @lccustno, @lcinvno
> while @@fetch_status <> -1
> begin
>
> if @@fetch_status = -2
> begin
> fetch next from curarinvc_upd into @lnsalesamt, @lndiscamt, @lnbalance,
> @lctype, @lnvoid, @lcinvcuid, @lccardno, @lcpaycode, @lcexpdate, @lccardname,
> @lccustno, @lcinvno
> continue
> end
> if @lnvoid = 1
> begin
> update arinvc
> set nfsalesamt = 0, nfdiscamt = 0, nftaxable1 = 0, nftaxable2 = 0,
> nffrtamt = 0, nftaxamt1 = 0, nftaxamt2 = 0, nftaxamt3 = 0, nfadjamt = 0,
> nffinamt = 0, nfbalance = 0, ntaxable1 = 0, ntaxable2 = 0, nsalesamt = 0,
> ndiscamt =0, nfrtamt = 0, nfinamt = 0, ntaxamt1 = 0, ntaxamt2 = 0, ntaxamt3 = 0,
> nadjamt = 0, nbalance = 0
> where cuid = @lcinvcuid
>
> end
>
> update arinvc set nfbalance = ((nfsalesamt -nfdiscamt) + nffrtamt + nffinamt +
> nftaxamt1 + nftaxamt2 + nftaxamt3 + nfadjamt) - ( nftotpaid + nftotdisc + nftotadj +
> nftotdebt), nbalance = ((nsalesamt -ndiscamt) + nfrtamt + nfinamt + ntaxamt1 +
> ntaxamt2 + ntaxamt3 + nadjamt) - ( ntotpaid + ntotdisc + ntotadj + ntotdebt + ntotmcvar +
> nmcround) where cuid = @lcinvcuid
>
> update arinvc set nmcround = nbalance, nbalance = 0 where nfbalance = 0
> and cuid = @lcinvcuid
>
> update arcust
> set arcust.natdsamt = arcust.natdsamt - (select nsalesamt - ndiscamt from deleted
>
> where ccustno = arcust.ccustno and cuid = @lcinvcuid) +
> (select nsalesamt-ndiscamt from arinvc
>
> where ccustno = arcust.ccustno and cuid = @lcinvcuid ),
> arcust.nbalance = arcust.nbalance - (select nbalance from deleted where ccustno
>
> = arcust.ccustno and cuid = @lcinvcuid) +
>
> (select nbalance from arinvc where ccustno = arcust.ccustno and cuid = @lcinvcuid)
> where arcust.ccustno = @lccustno
> if @lctype = "R"
> update arcust
> set arcust.nopencr = arcust.nopencr + (select nbalance from deleted where
> ccustno = arcust.ccustno and cuid = @lcinvcuid) -
> (select nbalance from arinvc where ccustno = arcust.ccustno
>
> and cuid = @lcinvcuid )
> where arcust.ccustno = @lccustno
>
> select @lnpaytype = (select npaytype from arpycd where cpaycode = @lcpaycode)
> if @lnpaytype <> 2
> update arinvc set cchkno = '' where cuid = @lcinvcuid
>
> if @lnpaytype <> 3
> update arinvc set ccardno = '' where cuid = @lcinvcuid
>
>
> if @lnpaytype = 5
> update arinvc set ntermdisc = (select ntermdisc from arpycd where
> cpaycode = arinvc.cpaycode), ndiscday = (select ndiscday from arpycd where
> cpaycode = arinvc.cpaycode), ndueday = (select ndueday from arpycd where
> cpaycode = arinvc.cpaycode) where cuid = @lcinvcuid
> else
> update arinvc set ntermdisc = 0, ndiscday = 0, ndueday = 0
> where cuid = @lcinvcuid
>
>
> if @lnvoid = 0 and @lccardno <> ''
> begin
>
> if not exists(select arcard.ccardno from arcard where
>
> arcard.ccustno = @lccustno and arcard.cpaycode = @lcpaycode and
> arcard.ccardno = @lccardno)
> begin
> execute vsp_am_assignuid @cretvalue = @lcuid output, @bfromfront = 0
> insert into arcard(cuid, ccustno, cpaycode, ccardno)
> values( @lcuid, @lccustno, @lcpaycode, @lccardno)
> end
> update arcard set cexpdate = @lcexpdate,
> ccardname = @lccardname where ccardno = @lccardno
> end
> fetch next from curarinvc_upd into @lnsalesamt, @lndiscamt, @lnbalance,
> @lctype, @lnvoid, @lcinvcuid, @lccardno, @lcpaycode, @lcexpdate,
> @lccardname, @lccustno, @lcinvno
> end
>
> close curarinvc_upd
> deallocate curarinvc_upd
>end
>
>Can anyone explain this message?
>
>TIA
Vlad Grynchyshyn, Project Manager, MCP
vgryn@yahoo.comICQ #10709245
The professional level of programmer could be determined by level of stupidity of his/her bugs
It is not appropriate to say that question is "foolish". There could be only foolish answers. Everybody passed period of time when knows nothing about something.