Number used for trigger maintenance/unused number

Original post address:

Http://community.csdn.net/Expert/topic/3298/3298074.xml? Temp =. 6988336.

Table1 is the initial data, Table2 is the used ticket

Write a trigger on Table2. When Table2 is inserted, updated, or deleted in batches, the 'used ticket number', 'used ticket number', and 'balance ticket number' of tabel1 are displayed in real time ', the value of 'balance votes.

Bytes -----------------------------------------------------------------------------------------------------------------------

-- Example

-- Sample Data
Create Table Table1 (No. Int, type varchar (10), start number char (7), end number char (7), total number int, used ticket number varchar (8000 ), used Number of votes int, remaining number of votes int, remaining number varchar (8000), combined number varchar (20 ))
Insert Table1 select 1, 'A', '000000', '000000', 10, null, 0000001, '2017-100', 'a-0000001-0000010'
Union all select 2, 'B', '000000', '000000', 10, null, 0000011, '2017-0000020 ',' B-0000011-0000020'

Create Table Table2 (combination number varchar (20), category varchar (10), ticket number char (7 ))
Go

-- Trigger
Create trigger tr_process on Table2
For insert, update, delete
As
Select id = identity (INT, 1, 1)
, A. combination number, A. Ticket number
, B. Start number, B. End number
, Used ticket number = cast (null as [varchar] (8000 ))
, Balance Ticket No. = cast (null as [varchar] (8000 ))
Into # T
From Table2 A, Table1 B
Where a. combination number = B. combination number
And (exists (select 1 from inserted where combination number = A. combination number)
Or exists (select 1 from deleted where combination number = A. combination number ))
Order by A. combination number, A. Ticket number

Declare @ combination number varchar (20), @ ticket number int
, @ Varchar (8000), @ varchar (8000)

Update # t set
@ Used ticket number = case
When combination number = @ combination number
Then case
When Ticket No. = @ Ticket No. + 1
Then case
When right (@ used ticket number, 1) = '-'
Then @ used ticket number + ticket number
Else left (@ used ticket number, Len (@ used ticket number)-7) + ticket number
End
Else case
When right (@ used ticket number, 1) = '-'
Then left (@ used ticket number, Len (@ used ticket number)-1)
Else @ used ticket number end + ',' + ticket number + '-'
End
Else ticket number + '-'
End,
@ Balance Ticket No. = case
When combination number = @ combination number
Then case
When Ticket No. = @ Ticket No. + 1
Then left (@ balance Ticket No., Len (@ balance Ticket No.)-8)
When right (9999999 + Ticket No., 7) + '-' = right (@ balance Ticket No., 8)
Then left (@ balance, Len (@ balance)-1) + ','
Else @ balance ticket number + right (9999999 + ticket number, 7) + ','
End + right (10000001 + ticket number, 7) + '-'
Else case
When start number = Ticket No.
Then''
When cast (Starting number as INT) + 1 = Ticket No.
Then start number + ','
Else start number + '-' + right (9999999 + ticket number, 7) + ','
End + right (10000001 + ticket number, 7) + '-'
End,
Used ticket No. = @ used ticket No,
Balance Ticket No. = @ balance Ticket No,
@ Ticket No. = Ticket No,
@ Combination number = combination number

Update a set
Used ticket No. = case
When right (B. used ticket number, 1) = '-'
Then left (B. used ticket number, Len (B. used ticket number)-1)
Else B. used ticket No.
End,
Balance Ticket No. = case
When B. Termination No. = B. Ticket No. + 1
Then left (B. Balance No., Len (B. Balance No.)-1)
When Len (B. Balance No.) = 8 and B. Termination No. <stuff (B. Balance No ,'')
Then''
When B. Termination No. <left (right (B. Balance No., 8), 7)
Then left (B. Balance No., Len (B. Balance No.)-9)
Else B. Balance Ticket No. + B. Termination No.
End,
Used votes = C. Used votes,
Remaining votes = A. Total-C. Used votes
From Table1 A, # t B ,(
Select id = max (ID), number of used votes = count (*)
From # T
Group by combination number
) C Where a. combination number = B. combination number
And B. ID = C. ID

-- Process all deleted data in the subtable
If exists (select 1 from deleted a where not exists (select 1 from Table2 where combination number = A. combination number ))
Update a set used ticket No. = '', used ticket No. = 0, balance Ticket No. = A. Start No. + '-' + A. Termination No.
From Table1 ,(
Select distinct combination number from deleted
Where not exists (select 1 from Table2 where combination number = A. combination number)
) B where a. combination number = B. combination number
Go

-- Insert 1st records
Insert Table2 select 'a-0000001-0000010 ', 'A', '123'

-- Display Results
Select No., used ticket No., used ticket No., remaining ticket No., combined No. From Table1
Go

-- Insert 2nd records
Insert Table2 select 'a-0000001-0000010 ', 'A', '123'

-- Display Results
Select No., used ticket No., used ticket No., remaining ticket No., combined No. From Table1
Go

-- Insert 3rd records
Insert Table2 select 'a-0000001-0000010 ', 'A', '123'

-- Display Results
Select No., used ticket No., used ticket No., remaining ticket No., combined No. From Table1
Go

-- Insert 4th records
Insert Table2 select 'a-0000001-0000010 ', 'A', '123'

-- Display Results
Select No., used ticket No., used ticket No., remaining ticket No., combined No. From Table1
Go

-- Modify record
Update Table2 set combination No. = 'B-0000011-0000020', Ticket No. = '123'
Where combination number = 'a-0000001-0000010' and ticket No. = '20140901'

-- Display Results
Select No., used ticket No., used ticket No., remaining ticket No., combined No. From Table1
Go

-- Batch delete:
Delete from Table2
Where Ticket No. In ('123', '123', '123 ')

-- Display Results
Select No., used ticket No., used ticket No., remaining ticket No., combined No. From Table1
Go

-- Delete test
Drop table Table1, Table2

/* -- View the result by yourself --*/