SSMS 2008: Confusing/Incorrect Error Message Take 2

Dear SQLers:

Take 2 on trying to resolve this:

WARNING: The script drops the database Banking.

Start SSMS. Load the following script. Execute the script. It
should execute fine with no errors except possibly that the database
Banking does not exist.

Drill down to Banking's Accounts table, right-click, and select
Edit Top 200 Rows. Move to "Main Account" in the ACName column,
replace it with "Main", and press down-arrow. The change should be
accepted.

Drill down to Banking's Subaccounts table, right-click, and
select Edit Top 200 Rows. Move to "Pocket S/A" in the SAName column,
replace it with "Pocket", and press down-arrow. BOOM! WHY?

Drill down to Banking's TranTypes table, right-click, and select
Edit Top 200 Rows. Move to "Cheque" in the TTName column, replace it
with "Check", and press down-arrow. BOOM! WHY?

The trigger code is very similar between the three tables. I
have selected analogous columns that NiceString() modifies in the same
way. Why does Accounts work and Subaccounts and TranTypes not work?

The complaint is that two rows would be modified, BUT when I have
inserted code in a trigger to check, I find that there is only one row
being modified.

Maybe, I am overlooking something trivial, but at this point, I
have no idea what.

***** Start of Code *****




use master
go

drop database Banking
go

create database Banking
go

use Banking
go





create table Accounts
(
ACUK nvarchar(4) primary key, -- arbitrary length
ACOrd int,
ACName nvarchar(40), -- arbitrary length
ACNr nvarchar(20) -- arbitrary length
)

create index ACOrd on Accounts(ACOrd,ACName)

go


create table Subaccounts
(
SAUK nvarchar(4) unique, -- arbitrary length
SAOrd int,
SAName nvarchar(40), -- arbitrary length
SAInACUK nvarchar(4), -- arbitrary length
foreign key (SAInACUK) references Accounts(ACUK)
)

create index SAOrder on Subaccounts(SAOrd,SAInACUK,SAName)

go


create table TranTypes
(
TTUK nvarchar(2) unique,
TTName nvarchar(40), -- arbitrary length
TTUsualSign nvarchar(1)
constraint ckTTUsualSign check(TTUsualSign in (N'D',N'C'))
)

go




of the

number of
delimiter):


create procedure NiceString
@strParm nvarchar(max) output, -- the string to make nice and
@mLines int=0, -- maximum number of lines permitted in string
string
string
@fCapitalise bit=0, -- capitalise? 1: yes, 0: no
@fTrimLead bit=1, -- trim leading blanks on each line? 1: yes,
0: no
@fTrimTrail bit=1 -- trim trailing blanks on each line? 1:
yes, 0: no
with recompile
as
begin
declare
@strWork nvarchar(max)=@strParm, -- working version of string
@strOut nvarchar(max)=N'', -- result string so far
@cLines int=1, -- number of lines (always at least one)
@strLine nvarchar(max), -- the current line being processed

@chrLineDelim nchar(1)=nchar(13), -- CR
@chrLF nchar(1)=nchar(10), -- LF
@chrTAB nchar(1)=nchar(9), -- TAB

@iLineDelim int, -- where next line delimiter is in @strWork

@cLeading int, -- how many leading spaces in line
@cTrailing int, -- how many trailing spaces in line

@strPrev nvarchar(max) -- previous version of line

if @fCapitalise=1
select @strWork=upper(@strWork)

while len(@strWork)>0 and (@mLines=0 or @cLines<=@mLines)
begin
select @iLineDelim=patindex(N'%'+@chrLineDelim+N'%',@strWork)
if @iLineDelim>0
begin
select @strLine=left(@strWork,@iLineDelim-1)
select
@strWork=substring(@strWork,@iLineDelim+1,len(@strWork))
select @cLines=@cLines+1
end
else
begin
select @strLine=@strWork
select @strWork=N''
end

select @strLine=replace(@strLine,@chrLF,N'')
select @strLine=replace(@strLine,@chrTAB,N' ')
trailing
if @fTrimLead=1 and @fTrimTrail=1
begin
select @cLeading=0
select @cTrailing=0
end
else
begin
select @cLeading=patindex(N'%[^ ]%',@strLine)
if @cLeading=len(@strLine) -- Line is only blanks.
select @cTrailing=0
else
select @cTrailing=patindex(N'%[^ ]%',reverse(@strLine))
end
select @strLine=ltrim(rtrim(@strLine))

select @strPrev=N''
while @strLine<>@strPrev
begin
select @strPrev=@strLine
select @strLine=replace(@strLine,N' ',N' ')
end

if @fTrimLead=0
select @strLine=space(@cLeading)+@strLine
if @fTrimTrail=0
select @strLine=@strLine+space(@cTrailing)

if len(@strOut)>0
select @strOut=@strOut+@chrLineDelim
select @strOut=@strOut+@strLine
end

if @mLines=0 or @cLines<=@mLines -- Return nice string if no
error.
select @strParm=@strOut
else
select @cLines=0 -- and @strParm is NOT changed.
return @cLines
end

go





create trigger trgIUAccounts on Accounts
instead of insert, update
as
begin
declare @AccessType int -- 0: row insert, 1: row update
if exists (select * from Deleted)
select @AccessType=1
else
select @AccessType=0

declare @RetVal int
declare @strWork nvarchar(max)

declare curInserted insensitive cursor
for select ACUK,ACOrd,ACName,ACNr from Inserted

declare
@ACUK nvarchar(4),
@ACOrd int,
@ACName nvarchar(40),
@ACNr nvarchar(20)

open curInserted

while 1=1
begin
fetch next from curInserted into @ACUK,@ACOrd,@ACName,@ACNr
if @@fetch_status<>0
break -- BREAK

select @strWork=@ACUK
execute @RetVal=NiceString @strWork output,1,1,1,1
if @RetVal=0
raiserror(N'ACUK: String has too many lines.',1,1)
select @ACUK=@strWork

select @strWork=@ACName
execute @RetVal=NiceString @strWork output,0,0,1,1
if @RetVal=0
raiserror(N'ACName: String has too many lines.',1,1)
select @ACName=@strWork

select @strWork=@ACNr
execute @RetVal=NiceString @strWork output,0,0,1,1
if @RetVal=0
raiserror(N'ACNr: String has too many lines.',1,1)
select @ACNr=@strWork

if @AccessType=0
insert into Accounts
(ACUK,ACOrd,ACName,ACNr)
values
(@ACUK,@ACOrd,@ACName,@ACNr)
else
update Accounts
set ACUK=@ACUK,ACOrd=@ACOrd,ACName=@ACName,ACNr=@ACNr
where ACUK=@ACUK
end

close curInserted
deallocate curInserted
end

go


create trigger trgIUSubaccounts on Subaccounts
instead of insert, update
as
begin
declare @AccessType int -- 0: row insert, 1: row update
if exists (select * from Deleted)
select @AccessType=1
else
select @AccessType=0

declare @RetVal int
declare @strWork nvarchar(max)

declare curInserted insensitive cursor
for select SAUK,SAOrd,SAName,SAInACUK from Inserted

declare
@SAUK nvarchar(4),
@SAOrd int,
@SAName nvarchar(40),
@SAInACUK nvarchar(4)

open curInserted

while 1=1
begin
fetch next from curInserted into @SAUK,@SAOrd,@SAName,@SAInACUK
if @@fetch_status<>0
break -- BREAK

select @strWork=@SAUK
execute @RetVal=NiceString @strWork output,1,1,1,1
if @RetVal=0
raiserror(N'SAUK: String has too many lines.',1,1)
select @SAUK=@strWork

select @strWork=@SAName
execute @RetVal=NiceString @strWork output,0,0,1,1
if @RetVal=0
raiserror(N'SAName: String has too many lines.',1,1)
select @SAName=@strWork

select @strWork=@SAInACUK
execute @RetVal=NiceString @strWork output,1,1,1,1
if @RetVal=0
raiserror(N'SAInACUK: String has too many lines.',1,1)
select @SAInACUK=@strWork

if @AccessType=0
insert into Subaccounts
(SAUK,SAOrd,SAName,SAInACUK)
values
(@SAUK,@SAOrd,@SAName,@SAInACUK)
else
update Subaccounts
set SAUK=@SAUK,SAOrd=@SAOrd,SAName=@SAName,SAInACUK=@SAInACUK
where SAUK=@SAUK
end

close curInserted
deallocate curInserted
end

go


create trigger trgIUTranTypes on TranTypes
instead of insert, update
as
begin
declare @AccessType int -- 0: row insert, 1: row update
if exists (select * from Deleted)
select @AccessType=1
else
select @AccessType=0

declare @RetVal int
declare @strWork nvarchar(max)

declare curInserted insensitive cursor
for select TTUK,TTName,TTUsualSign from Inserted

declare
@TTUK nvarchar(2),
@TTName nvarchar(40),
@TTUsualSign nvarchar(1)

open curInserted

while 1=1
begin
fetch next from curInserted into @TTUK,@TTName,@TTUsualSign
if @@fetch_status<>0
break -- BREAK

select @strWork=@TTUK
execute @RetVal=NiceString @strWork output,1,1,1,1
if @RetVal=0
raiserror(N'TTUK: String has too many lines.',1,1)
select @TTUK=@strWork

select @strWork=@TTName
execute @RetVal=NiceString @strWork output,0,0,1,1
if @RetVal=0
raiserror(N'TTName: String has too many lines.',1,1)
select @TTName=@strWork

select @strWork=@TTUsualSign
execute @RetVal=NiceString @strWork output,1,1,1,1
if @RetVal=0
raiserror(N'TTUsualSign: String has too many lines.',1,1)
select @TTUsualSign=@strWork

if @AccessType=0
insert into TranTypes
(TTUK,TTName,TTUsualSign)
values
(@TTUK,@TTName,@TTUsualSign)
else
update TranTypes
set TTUK=@TTUK,TTName=@TTName,TTUsualSign=@TTUsualSign
where TTUK=@TTUK
end

close curInserted
deallocate curInserted
end

go



Some
cases.


insert into Accounts
(ACUK,ACOrd,ACName,ACNr)
values
(N' inc',0,N' Income Account ',N'12345-6 01'),
(N'Main',10,N' Main ',N'12345-6 02')

insert into Accounts
(ACUK,ACOrd,ACName,ACNr)
values
(N'SAVE',20,N'Savings ',N'12345-6 11')

update Accounts
set ACName=N'Main Account'
where ACUK=N'MAIN'

update Accounts
set ACOrd¬Ord+10


insert into Subaccounts
(SAUK,SAOrd,SAName,SAInACUK)
values
(N'inc',0,N' Income ',N'INC'),
(N'ibe',80,N'Income Account Banking Expenses S/A',N'INC'),
(N'IINT',85,N'Income Account Interest',N'INC'),
(N'PKT',10,N'Pocket S/A',N'MAIN'),
(N'RENT',20,N'Rent S/A',N'MAIN'),
(N'PH',30,N'Phone S/A',N'MAIN'),
(N'MBE',90,N'Main Account Banking Expenses S/A',N'MAIN'),
(N'MINT',95,N'Main Interest',N'MAIN'),
(N'RES',10,N'Reserves',N'SAVE'),
(N'ITSA',20,N'Income Tax S/A',N'SAVE')

insert into Subaccounts
(SAUK,SAOrd,SAName,SAInACUK)
values
(N'SINT',95,N'Savings Account Interest',N'SAVE')

update Subaccounts
set SAOrd=SAOrd+10
where SAInACUK=N'INC'

update Subaccounts
set SAName=N'Main Account Interest'
where SAUK=N'MINT'


insert into TranTypes
(TTUK,TTName,TTUsualSign)
values
(N'dp',N' Deposit ',N'd'),
(N'WD',N'Withdrawal',N'c'),
(N'CH',N'Cheque',N'C'),
(N'TI',N' Trans. In ',N'D'),
(N'TO',N'Trans. Out',N'C'),
(N'DR',N'Debit',N'D'),
(N'CR',N'Credit',N'C'),
(N'IN',N'Interest',N'D')

insert into TranTypes
(TTUK,TTName,TTUsualSign)
values
(N'BC',N'Banking Charge',N'D')

update TranTypes
set TTName=N'Transfer '+substring(TTName,8,len(TTName))
where left(TTName,7)=N'Trans. '

update TranTypes
set TTUsualSign=N'C'
where TTUK=N'BC'

go





go




select *
from Accounts
order by ACOrd,ACName

select Subaccounts.*,ACOrd
from Subaccounts
join Accounts on SAInACUK¬UK
order by ACOrd,SAOrd,SAName

select *
from TranTypes
order by TTUK




/* *****
***** */
go




use master
go
***** End of Code *****

Sincerely,

Gene Wirchenko