Original post:
http://community.9cbs.net/expert/topic/3298/3298074.xml?temp=.6988336
Table1 is initialized data, Table2 is the use of the paper
Write the trigger on table2, Table2 Each INSERT, UPDATE, OR Batch DELETE, real-time reflecting the value of Tabel1's 'already used ticket number,' has used votes ',' balance ticket number, 'balance ticket number'.
-------------------------------------------------- -------------------------------------------------- -------------------
- 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 Vote int, balance ticket number varchar (8000), combination number varchar (20)) Insert Table1 Select 1, 'A', '0000001', '0000010', 10, NULL, 0, 10, '0000001-0000010', ' A-0000001-0000010 'ELON All SELECT 2,' B ',' 0000011 ',' 0000020 ', 10, NULL, 0, 10,' 0000011-0000020 ',' B-0000011-0000020 '
Create Table Table2 (Number VARCHAR (20), Category Varchar (10), Ticket CHAR (7)) GO
- Trigger Create Trigger Tr_Process On Table2for INSERT, UPDATE, DELETEASSELECT ID = Identity (int, 1, 1), a. Combination number, a. Ticket number, b. Start number, b. Termination number, have been used = CAST (NULL AS [VARCHAR] (8000)), balance ticket number = Cast (null as [varchar] (8000)) INTO #TFROM TABLE2 A, TABLE1 BWHERE A. Combination number = b. Combination number and (exists 1 from inserted where combination number = a. Combined number) OR Exists (SELECT 1 from deleted where combination number = a. Combination number)) Order by a. Combination number, a. Ticket number
Declare @ 合 编号 VARCHAR (20), @ 号 @ @ 已 号 VARCHAR (8000), @ 结余 VARCHAR (8000)
Update #t set @ d 用 号 = Case WHEN combination number = @ 合 合号 = @ 号 1 Ten Case When Right (@ 已 用号, 1) = '-' Then @ 已 用Ticket number ticket number Else Left (@ 已 用 号, LEN (@ 已 号) -7) Ticket number ELSE CASE WHEN RIGHT (@ 已 号, 1) = '-' Then Left (@ Ticket number, LEN (@ 用 号) -1) Else @ 用 号 END ',' ticket number '-' end else ticket number 'end, @ 结余 号 = Case When Combination number = @ 合 合号 = @ 号 1 Then Left (@ 结余, LEN (@ 结余 号) -8) WHEN Right (9999999 ticket number, 7) '-' = Right (@ 结 号, 8) Then Left (@ 结余 号, LEN (@ 结余 号) -1) ',' Else @ 结余 号 Right (9999999 ticket number, 7) ',' END RIGHT (10000001 ticket number, 7) '-' Else Case When start number = Ticket number Then '' When Cast (start number as int) 1 = Ticket number THEN start number ',' else Start number '-' Right (9999999 ticket number, 7) ',' end right (10000001 ticket number, 7) '-' end, the ticket number = @ has been used ticket number, balance Ticket number = @ 结 余 号号, @ 号 = ticket number, @ combination number = combination number UPDATE A SET has been used ticket number = Case When Right (b. Used ticket number, 1) = '-' Then Left (B Ticket number, Len (b. Has been used ticket number) -1) Else B. End of the ticket number End, the balance ticket number = case when b. Terminal number = b. Ticket number 1 Then LEFT (b. Balance Ticket number, LEN (B. balance ticket number) -1) when len (b. Balance ticket number) = 8 and b. Termination number - Processing Data IF EXISTS (SELECT 1 from deleted a where not exists) in the subcourse (SELECT 1 from table2 where combination number = a. Combination number)) Update A SET has been used with ticket number = '', Number of tickets = 0, the number of balances = a. Total number, balance ticket number = a. Start number '-' a. Termination number from table1 a, (Select Distinct combination number from deleted a where not exists "(SELECT 1 from Table2 WHERE combination number = a. combination number)) B WHERE a. Composite number = b. Combination number GO - Insert Article 1 Record INSERT TABLE2 SELECT 'A-000000001-0000010', 'A', '0000001' - Display Results SELECT serial number, used ticket number, number of votes, balance ticket, balance ticket number, combination number from table1go - Inserticle 2 Record Insert Table2 SELECT 'A-0000001-0000010', 'A', '0000002' - Display result Select serial number, use ticket number, number of votes, balance ticket, balance ticket number, combination number from table1go - Insert Article 3 Record Insert Table2 SELECT 'A-0000001-0000010', 'A', '0000004' - Display result Select serial number, use ticket number, number of votes, balance ticket, balance ticket number, combination number from table1go - Insert Article 4 Record Insert Table2 SELECT 'A-0000001-0000010', 'A', '0000003' - Display result Select serial number, use ticket number, number of votes, balance ticket, balance ticket number, combination number from table1go - Modify record UPDATE TABLE2 SET combination number = 'b-0000011-0000020', ticket number = '0000011'where combination number =' A-0000001-0000010 'and ticket number =' 0000002 ' - Display result Select serial number, use ticket number, number of votes, balance ticket, balance ticket number, combination number from table1go - Batch deletion: delete from table2 where ticket number in ('0000001', '000000002', '0000011') - Display result Select serial number, use ticket number, number of votes, balance ticket, balance ticket number, combination number from table1go - Delete Table Table Table1, Table2 / * - Result yourself - * /