XPost: microsoft.public.sqlserver.programming
From: genew@ocis.net
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 (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 is a line
delimiter):
-- N'' has one line.
-- N'kitty' has one line.
-- N'This is the first line,
-- and this is the last line.' has two lines.
-- N'This is the first line,
-- this is the middle line,
-- 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
[continued in next message]
--- SoupGate-Win32 v1.05
* Origin: you cannot sedate... all the things you hate (1:229/2)
|
