Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!aioe.org!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From: Gene Wirchenko <genew@ocis.net>
Newsgroups: comp.databases.ms-sqlserver,microsoft.public.sqlserver.programming
Subject: SSMS 2008: Confusing/Incorrect Error Message Take 2
Date: Thu, 14 Apr 2011 15:29:55 -0700
Organization: A noiseless patient Spider
Lines: 612
Message-ID: <emseq6t05kacde727krpm75une3e3u42ar@4ax.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Injection-Info: mx02.eternal-september.org; posting-host="7Qrvczazr82YckO5XW8Vtw"; logging-data="13299"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX19IxWPJDxd7S30ZKLj55+BGRaCkhZcSwLU="
X-Newsreader: Forte Agent 4.2/32.1118
Cancel-Lock: sha1:h2L06GfnlaIIQZ69Z2LQe81SrW8=
Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:181

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 *****
-- Banking Learning Database
-- Version of 2011-04-14 15:16



--
-- Create Database
--

use master
go

drop database Banking
go

create database Banking
go

use Banking
go



--
-- Create Tables and Indexes
--

-- Bank Accounts Table

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

-- Subaccounts Table
-- Some are set-asides (S/A).

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

-- Transaction Types Table

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

go



--
-- Stored Procedures
--

-- NiceString
-- This procedure makes a string "nice".  "nice" means that each line
of the
-- string has:
--   1) no occurrences of <tab> (each replaced with space), and
--   2) no leading spaces (removed) (optional),
--   3) no trailing spaces (removed) (optional),
--   4) no two spaces in a row (squeezed to one space).
-- The four points are applied in order 1-4.

-- "cLines": count of lines in the string.  This is defined as the
number of
-- line delimiters in the string + 1. e.g. (where <LD> is a line
delimiter):
--      N'' has one line.
--      N'kitty' has one line.
--      N'This is the first line,<LD>
--        and this is the last line.' has two lines.
--      N'This is the first line,<LD>
--        this is the middle line,<LD>
--        and this is the last line.' has three lines.

-- If mLines=0, returns cLines.
-- If mLines>0 and cLines<=mLines, returns cLines.
-- If mLines>0 and cLines>mLines, returns 0.  (Even an empty string is
-- considered to have one line.)

create procedure NiceString
 @strParm nvarchar(max) output,     -- the string to make nice and
                                    -- if no error, the nice result
 @mLines int=0,     -- maximum number of lines permitted in string
  -- If 0, the string can have any number of lines.  If >0 and the
string
  -- turns out to have more lines, processing will be aborted and the
string
  -- will not be changed.
 @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
      -- Get the next line and remove it from strWork.
      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

      -- Trimming and removal of tabs
      select @strLine=replace(@strLine,@chrLF,N'')
      select @strLine=replace(@strLine,@chrTAB,N' ')
      -- If either end is not to be trimmed, then the leading and
trailing
      -- spaces will have to be restored after the line is squeezed.
      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))

      -- Compression of consecutive spaces
      select @strPrev=N''
      while @strLine<>@strPrev
         begin
         select @strPrev=@strLine
         select @strLine=replace(@strLine,N'  ',N' ')
         end

      -- Add back any leading or trailing spaces being kept.
      if @fTrimLead=0
         select @strLine=space(@cLeading)+@strLine
      if @fTrimTrail=0
         select @strLine=@strLine+space(@cTrailing)

      -- Add modified line to @strOut.
      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



--
-- Table Triggers
--
-- The insert and update triggers are very similar between the tables.

-- Accounts Table

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

   -- NiceString() Values
   declare @RetVal int
   declare @strWork nvarchar(max)

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

   -- Current Row's Column Values
   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
       -- one line, capitalise
      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

-- Subaccounts Table

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

   -- NiceString() Values
   declare @RetVal int
   declare @strWork nvarchar(max)

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

   -- Current Row's Column Values
   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
       -- one line, capitalise
      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

-- Transaction Types Table

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

   -- NiceString() Values
   declare @RetVal int
   declare @strWork nvarchar(max)

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

   -- Current Row's Column Values
   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
       -- one line, capitalise
      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
       -- one line, capitalise
      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



--
-- Inserts
-- Inserts are split into multiple- and single-row cases to test both.
Some
-- values are adjusted to test updates in multiple- and single-row
cases.
--

-- Accounts Table

-- Multiple Insert
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')

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

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

-- Multiple Update
update Accounts
set ACOrd=ACOrd+10

-- Subaccounts Table

-- Multiple Insert
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')

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

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

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

-- Transaction Types Table

-- Multiple Insert
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')

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

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

-- Single Update
update TranTypes
set TTUsualSign=N'C'
where TTUK=N'BC'

go



--
-- Further Testing Inserts
--

-- None at present

go



--
-- Get Results
--

select *
from Accounts
order by ACOrd,ACName

select Subaccounts.*,ACOrd
from Subaccounts
join Accounts on SAInACUK=ACUK
order by ACOrd,SAOrd,SAName

select *
from TranTypes
order by TTUK



--
-- Scratch Pad Section
--

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



--
-- Quit
--

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

Sincerely,

Gene Wirchenko