Automatically generate residual number strings

Original post:

Http://community.9cbs.net/expert/topic/3220/3220744.xml?temp=.6029474

Problem Description:

INSERT main table select 'q', 0000001, 0000100, 100, '0000001-0000, 000000001, 0000200, 200,' 0000001-0000200 '

INSERT Table SELECT 'Q', 0000011, 0000030, 20Union All Select 'Q', 0000032,0000050, 19Union All Select 'Q', 0000061,0000080, 20UNION All Select 'M M', 000000001,0000100, 100

How to use triggers to update the balance field of the primary table when inserting each record in a subtray?

The result is: ID Name Strat End Amount Balance1 Q 0000001 0000100 41 0000001-0000010,0000031-0000031,0000051-0000060, 0000081-0000100 2 M 0000001 0000200 100 0000101-0000200

-------------------------------------------------- --------------------------------

- Test data Create Table [DBO]. [Master Table] ([Id] [INT] Identity (1, 1) Not Null, [Name] [varchar] (50) NULL, [STRAT] [varchar] (7) NULL, [END] [VARCHAR] (7) NULL, [AMOUNT] [INT] null, [Balance] [varchar] (200) NULL)

Create Table [DBO]. [Sum Table] ([Id] [INT] Identity (1, 1) Not Null, [Name] [varchar] (50) NULL, [Strat] [varchar] (7) NULL, [END ] [varchar] (7) null, [Amount] [INT] NULL)

INSERT main table select 'q', '0000001', '0000100', 100, '0000001-0000200', 200000000001-0000200 ', 2000000001-0000200'go

- Processing trigger Create Trigger Tr_Process ON Table for Insert, Update, Deleteasselect *, [Balance] = Cast (NULL AS [varchar] (200)) INTO #TFROM subthelet awhere exists (SELECT 1 from inserted where name = a.name) or exists (SELECT 1 from deleted where name = a.name) Order by name, [Strat]

Declare @name varchar (10), @ End int, @ RE VARCHAR (200) Update #t set @ re = case @name when name kil1 @ End 1 <[strat] Ten @re ',' Right ( 10000001 @ END, 7) '-' Right (9999999 [Strat], 7) Else @ RE '' end else '' end, @ end = [end], @ name = name, [balance] = @ Reupdate a set [balance] = case when a. [strat] 'Then Stuff (b. [balance], 1, 1,') ELSE 'END END Case WHEN B. [end] 'Then', 'else' end right (10000001 b. [end], 7) '-' a. [end] else '' end, [amount] = cast (a. [end] as int) -A. [start] -b. [Amount] 1FROM primary table A, (select name, [strat] = min ([strat]), [end] = max ([end]), [balance] = max ([balance]), [Amount] = SUM Amount]) from #t group by name) b Where a.name = B.Name

- Processing data IF EXISTS (SELECT 1 from Deleted a where not exists) in the subtray (SELECT 1 from deleded a where not exists ") Update a set amount = cast (a. [End] AS INT) -A. [Strat] 1, [Balance] = a. [Strat] '-' a. [end] from the primary table A, (Select Distinct Name from Deleted a Where not exists) Table where name = a.name)) B Where a.name = B.Namego

- Insert subk table data INSERT Table Select 'q', '0000011', '00000', 20Union All Select 'q', '0000032', '000000', 19Union All Select 'q', '0000061', '0000080 ', 20UNION All SELECT' M ',' 0000051 ',' 0000100 ', 50 - Update Update Sub Table Set Name =' M'Where ID = 1

Delete from table where id in (1, 3, 4) Go

- Display processing results Select * from the main table Select * from child table Go

DROP TABLE main table, child table

/*--Test Results

ID Name Strat End Amount Balance ----------------------------------------- ------------------ 1 q 0000001 0000100 81 N00001-0000031, 0000051-0000200 20000001 0000200 20000001-0000200

(The number of rows affects is 2 lines)

ID Name Strat End Amount ----------- ------ ---------------------- 2 Q 0000032 0000050 19

(The number of rows affects is 1 line) - * /