Trigger maintenance has been usedunused

xiaoxiao2021-03-06 99

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'