August 1, 2002 at 4:35 pm
Huge post here (includes large trigger code). This trigger parses a doc_name field into its string and integer parts and stores these parts in a series of fields named S1, I1, S2, I2, ... ,S8, I8. I am trying to do an INSERT into the table using a SELECT which results in 100,000 or more rows being inserted and would like the trigger to handle this. How do I implement multirow processing for this trigger? Thanks!
==================== The Trigger ======================
CREATE TRIGGER TI_PLNT_NE on dbo.PLNTDOCS_NE
FOR INSERT
AS
begin
declare
@Doc_Name char(30),
@Trim_Doc_Namevarchar(30),
@S1varchar(30),
@I1varchar(30),
@S2varchar(28),
@I2varchar(30),
@S3varchar(26),
@I3varchar(30),
@S4varchar(24),
@I4varchar(30),
@S5varchar(22),
@I5varchar(30),
@S6varchar(20),
@I6varchar(30),
@S7varchar(18),
@I7varchar(30),
@S8varchar(16),
@I8varchar(30),
@CharCntint,
@NumCntint,
@CurrTypechar,
@AlphaCntint,
@Charchar,
@Tempvarchar(30),
@Lengthint,
@IsNumint
select@Doc_Name = inserted.doc_name
from inserted
Select @Trim_Doc_Name=rtrim( @Doc_Name)
Select @CharCnt = 1, @NumCnt = 0, @AlphaCnt = 0
Select @Char = substring( @Trim_Doc_Name, @CharCnt, 1)
Select @IsNum=ascii(@Char) - ascii('0')
if @IsNum >= 0
if @IsNum < 10
Select @IsNum = 1
else
Select @IsNum = 0
else
Select @IsNum = 0
if @IsNum = 1
begin
Select @CurrType = 'N'
Select @NumCnt = 1
Select @AlphaCnt = 1
Select @S1 = ' '
end
else
begin
Select @CurrType = 'A'
Select @AlphaCnt = 1
end
Select @Temp = @Char
Select @Length=datalength(@Trim_Doc_Name)
LOOP:
if @CharCnt > @Length
GOTO FINISH
Select @CharCnt = @CharCnt + 1
Select @Char = substring( @Trim_Doc_Name, @CharCnt, 1)
Select @IsNum=ASCII(@Char) - ASCII('0')
if @IsNum >= 0
if @IsNum < 10 Select @IsNum = 1
else Select @IsNum = 0
else Select @IsNum = 0
if @IsNum = 1
begin
if @CurrType = 'N'
begin
Select @Temp=@Temp+@Char
end
else
begin
if @AlphaCnt = 1
Select @S1=@Temp
else
if @AlphaCnt = 2
Select @S2=@Temp
else
if @AlphaCnt = 3
Select @S3=@Temp
else
if @AlphaCnt = 4
Select @S4=@Temp
else
if @AlphaCnt = 5
Select @S5=@Temp
else
if @AlphaCnt = 6
Select @S6 = @Temp
else
if @AlphaCnt = 7
Select @S7=@Temp
else
if @AlphaCnt = 8
Select @S8=@Temp
Select @NumCnt = @NumCnt + 1
Select @Temp=@Char
Select @CurrType = 'N'
end
end
else
begin
if @CurrType = 'A'
begin
Select @Temp=@Temp+@Char
end
else
begin
if @NumCnt = 1
Select @I1=@Temp
else
if @NumCnt = 2
Select @I2=@Temp
else
if @NumCnt = 3
Select @I3=@Temp
else
if @NumCnt = 4
Select @I4=@Temp
else
if @NumCnt = 5
Select @I5=@Temp
else
if @NumCnt = 6
Select @I6 = @Temp
else
if @NumCnt = 7
Select @I7=@Temp
else
if @NumCnt = 8
Select @I8=@Temp
Select @AlphaCnt = @AlphaCnt + 1
Select @Temp=@Char
Select @CurrType = 'A'
end
end
GOTO LOOP
FINISH:
if @CurrType = 'N'
begin
if @NumCnt = 1
Select @I1=@Temp
else
if @NumCnt = 2
Select @I2=@Temp
else
if @NumCnt = 3
Select @I3=@Temp
else
if @NumCnt = 4
Select @I4=@Temp
else
if @NumCnt = 5
Select @I5=@Temp
else
if @NumCnt = 6
Select @I6 = @Temp
else
if @NumCnt = 7
Select @I7=@Temp
else
if @NumCnt = 8
Select @I8=@Temp
end
else
begin
if @AlphaCnt = 1
Select @S1=@Temp
else
if @AlphaCnt = 2
Select @S2=@Temp
else
if @AlphaCnt = 3
Select @S3=@Temp
else
if @AlphaCnt = 4
Select @S4=@Temp
else
if @AlphaCnt = 5
Select @S5=@Temp
else
if @AlphaCnt = 6
Select @S6 = @Temp
else
if @AlphaCnt = 7
Select @S7=@Temp
else
if @AlphaCnt = 8
Select @S8=@Temp
end
update plntdocs_ne
setS1 = @S1,
I1 = convert( int ,@I1),
S2 = @S2,
I2 = convert( int, @I2),
S3 = @S3,
I3 = convert( int, @I3),
S4 = @S4,
I4 = convert( int, @I4),
S5 = @S5,
I5 = convert( int, @I5),
S6 = @S6,
I6 = convert( int, @I6),
S7 = @S7,
I7 = convert( int, @I7),
S8 = @S8,
I8 = convert( int, @I8)
where doc_name = @Doc_Name
end
August 1, 2002 at 8:45 pm
Triggers operate on the entire result set, and are, by nature multi-row.
Based on your trigger, you may want to look at the CASE statement, which should provide you the functionality you need. An example of it's use is:
SELECT
FirstName
, LastName
, CASE
WHEN Country = 'USA' THEN 'North America'
WHEN Country = 'Canada' THEN 'North America'
WHEN Country = 'Mexico' THEN 'North America'
ELSE 'Outside of North America'
END Location
FROM Contributors
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply