Original post:
http://community.9cbs.net/expert/topic/3269/3269542.xml?temp=8.726138e-02
Table1 serial number category start number termination number Total number of ticket number has been used votes balance ticket number bang ticket number combination number (primary key) 1 a 000000000000003 20000002000000001-00005 3 A-0000001-0000005 2 B 0000006 00000010 5 0000006, 10000008 2 0000007,0000009,0000010 3 B-0000006-0000010Table2 combination number Category ticket number a-0000001-0000001-0000005 A 0000003B-0000006-00000 B 0000006B-0000006-00000 B 0000008
Hione is updated in the Table2 write trigger, the number of tickets, the number of votes, the balance ticket, and the number of balance tickets are used.
-------------------------------------------------- -------------------------------------------------- ----------------
- Example
- Example Data Create Table Table1 (Sequence INT, Category VARCHAR (10), Start No. CHAR (7), Termination Number Char (7), Total INT, Ticket Number VARCHAR (8000), Votes INT, Explosion Ticket number VARCHAR (8000), balance ticket int, combination number VARCHAR (20)) Insert Table1 Select 1, 'A', '000000001', '00000000000000000000000000000000000000000000004 0000005 ', 5,' A-0000001-0000005' EL SELECT 2, 'B', '0000006', '0000010', 5, '', 0, '0000006, 0000007, 0000006, 0000009, 0000010', 5 , 'B-0000006-0000010'
Create Table Table2 (Number VARCHAR (20), Category Varchar (10), Ticket CHAR (7)) GO
- Processing trigger Create Trigger Tr_Process On Table2For Insert, Update, DeleteasDeclare @ 组号 号 VARCHAR (20), @ sta varcha (8000), @ nma int, @ stb varchar (8000), @ nmb int
- Handle the deleted Select D. Combination number, D. ticket number, the ticket number = ',' a. Has been used ticket number ', a. Has been used, the number of votes, the balance ticket number = a. Balance Ticket number, a. Balance ticket INTO #d from deleted d. Combine number = a. Combination number ORDER BY D. Combination number, d. Ticket number
IF @@ rowcount> 0begin update #d set @ sta = replace (case combination number when @ 合 合号 @sta else has been used with ticket number End, ',' ticket number ',', '), @nma = Case combination number when @ 合 合号 @ nma-1 ELSE The number of votes - 1 end, @ stb = case combination number WHEN @ 合 合号 @ STB ',' ELSE Case balance ticket when '' Then 'ELSE balance Ticket number ',' end end ticket number, @ nmb = case combination number when @ 合 合号 = 1 END, @ 组合号 = combination number, used ticket number = @ STA, used Votes = @ nma, balance ticket number = @ STB, balance ticket = @ nmbupdate a set has been used ticket number = case b. The number of votes when 0 Then 'Else Substring (b. Has been used ticket number, 2, len (B) Ticket number) -2) END, the number of tickets has been used = B. The number of votes, the balance ticket number = b. Balance ticket number, the number of balances = b. Balance tickets from table1 a, # db, (select combination number, Number of votes = min (number of votes) from #d group BY combination number) c where a. Combined number = b. Combinations Number and c. Combine number = b. Combinations number and c. Number of votes = b. Votes END
- Handling new SELECT I. Combination number, I. Ticket number, used ticket number = a. Has been used, a. Has been used, the number of votes, the balance ticket number = ',' a. Balance ticket number ' , ', a. balance ticket INTO #IFROM INSERTED I, TABLE1 AWHERE I. Combination No. = a. Combined No. ORDER BY I. Composite Number, I. Voice number
IF @@ rowcount> 0begin set @ combination number = null update #i set @ sta = case combination number when @ 合 合号 @ Sta ',' Else Case Used Ticket number when '' Ten 'Else has been used with ticket number ',' end end ticket number, @ nma = case combination number when @ 组合 number THEN @ nma 1 ELSE The number of votes 1 end, @ stb = replace (CASE combination number WHEN @ 合 合号 @Sstb Else balance ticket number End, ',' ticket number ',', ','), @ nmb = case combination number WHEN @ 合 合号 @ NMB-1 ELSE balance ticket number -1 End, @ combination number = combination number, Tickets号 = @ STA, the number of votes = @ nma, the balance ticket number = @ STB, the number of balances = @ NMB
Update a set The ticket number = b. has been used, the number of votes is = b. Expere ticket, balance ticket number = case b. balance tickets when 0 Then '' else substring (b. balance ticket number, 2, LEN (b. balance ticket number) -2) End, balance ticket number = b. Balance tickets from table1 a, # ib, (select combination number, number of votes = max) from #i group BY combination number) c where a. Combined number = b. Combinations number and c. combine number = b. Combinations number and c. Silk tickets = b. Most of the ticket EndGo - Insert data Table2 Select 'A-0000001-0000005', 'A', '0000001'Union all Select' A-000000001-0000005 ',' A ',' 0000003'Union All Select 'B-0000006-00000', 'B', '0000000000006 B-0000006 -0000010 ',' b ',' 0000008 '
- Delete Test Delete from Table2 Where Show number = '0000008'
- Update Test Update Table2 Set Ticket No. = '0000008' Where Ticket Number = '0000006'go
- Display processing results Select * from table1go
- Delete Table Table Table1, Table2
/ * - Test results (see yourself) - * /
