SQL table Synchronization Update trigger

SQLTable Synchronization Update trigger

SQL Server 2000 trigger , table Synchronization Update
there are three tables, A , B , C
A , B The table contains : a1, B1, C1 three fields ,
C table Storage A , B table A1 , B1 , C1 set ,
all field types are nvarchar (10 ),
when data in table A is updated, deleted, or inserted, it must be reflected in C table.
when data in table B is updated or deleted and insert to the C table.
assume , B table A1, B1, c1 has a unique index

This problem can be easily solved theoretically, because it can be seen from the requirements, essentiallyCThe data stored in the table isA,BThe Union of tables. You canA,BCreate the sameTrigger, OnceA,BTable changes. For example, when a table is inserted, deleted, or updated, it is cleared.CTable dataA,BTable DataUnionInsert laterCTable to achieve the goal Hehahaha...

The followingTriggerThe implementation principle is:

WhenACheckCWhether the table hasAThe data to be inserted in the table. If no data exists, this row of data is insertedCTable. Otherwise, no operation is required.

when A table Update /span> , check B whether the row of data before update exists in the table. If yes, then, the C table should retain this row of data and update the A table the data is also inserted into the C table. If the B table does not have A table before update, and C the table does not have A This row of data after the table is updated, use A updated table data C the data in the table is the same as that in the row before the A table is updated; if B the table does not have A This row of Data updated by the table and C The table contains A This row of data after the table is updated, C delete data in the table that is the same as that in the A table before Update (because of update A after the table, A table and B table none span> A the row before the table is updated, this row of data is obviously in the C table and should not exist again ).

WhenAWhen the table is deleted, checkBWhether the table still existsAThe row of data to be deleted from the table. If yes, the row cannot be deleted.CTable andAThe same row of data to be deleted in the table. Otherwise, delete the instance.

BTableTriggerAndAThe principles in the table are the same.

Create trigger sync_c_by_a
On
After insert, update, delete
As
Declare @ DML tinyint -- 1: insert 2: Update 3: delete
Declare @ rowsd int
Declare @ rowsi int
Declare @ a1_d nvarchar (10)
Declare @ b1_d nvarchar (10)
Declare @ c1_d nvarchar (10)
-- Determine which type DML Operation
Select @ rowsd = count (*) from deleted
Select @ rowsi = count (*) from inserted
If @ rowsd = 0 and @ rowsi = 0
Goto exit _
If @ rowsd = 0 and @ rowsi> 0
Set @ DML = 1
Else
If @ rowsd> 0 and @ rowsi> 0
Set @ DML = 2
Else
If @ rowsd> 0 and @ rowsi = 0
Set @ DML = 3
If @ DML = 1
Begin
-- Check C Whether the table already exists A Data rows inserted in the table , If no , Insert
If not exists (select top 1 1 from C, inserted I where c. A1 = I. A1 and C. b1 = I. B1 and C. C1 = I. C1)
Insert into C select * From inserted
End
If @ DML = 2
Begin
-- Check B Whether the table has A This row of data before table update , If , You do not need to update C Table Data , Instead A The updated row of data in the table is inserted C Table
If not exists (select top 1 1 from B, deleted d Where B. A1 = D. A1 and B. b1 = D. B1 and B. C1 = D. C1)
Begin
-- If C The table does not exist. A This row of updated table data , Update C Table and A The data in the row before the table update is the same.
If not exists (select top 1 1 from C, inserted I where c. A1 = I. A1 and C. b1 = I. B1 and C. C1 = I. C1)
Begin
Update C set a1 = I. a1, B1 = I. b1, C1 = I. c1 from C, inserted I, deleted d Where C. a1 = D. a1 and C. b1 = D. b1 and C. c1 = D. c1
End
-- If C Table exists A This row of updated table data , Delete C Table and A The same row of data before table update
Else
Begin
Select @ a1_d = A1, @ b1_d = b1, @ c1_d = C1 from deleted
Delete from C where @ a1_d = A1 and @ BD d = b1 and @ c1_d = C1
End
End
Else
Insert into C select * From inserted I where not exists (select 1 from C where I. a1 = C. a1 and I. b1 = C. b1 and I. c1 = C. c1)
End
If @ DML = 3
Begin
-- If B The table does not exist. A The row of data to be deleted in the table. , You need C This row of data is deleted from the table.
If not exists (select top 1 1 from B, deleted d Where B. A1 = D. A1 and B. b1 = D. B1 and B. C1 = D. C1)
Delete from C where exists (select 1 from deleted d Where C. A1 = D. A1 and C. b1 = D. B1 and C. C1 = D. C1)
End
Exit _:

Create trigger sync_c_by_ B
On B
After insert, update, delete
As
Declare @ DML tinyint -- 1: insert 2: Update 3: delete
Declare @ rowsd int
Declare @ rowsi int
Declare @ a1_d nvarchar (10)
Declare @ b1_d nvarchar (10)
Declare @ c1_d nvarchar (10)
-- Determine which type DML Operation
Select @ rowsd = count (*) from deleted
Select @ rowsi = count (*) from inserted
If @ rowsd = 0 and @ rowsi = 0
Goto exit _
If @ rowsd = 0 and @ rowsi> 0
Set @ DML = 1
Else
If @ rowsd> 0 and @ rowsi> 0
Set @ DML = 2
Else
If @ rowsd> 0 and @ rowsi = 0
Set @ DML = 3
If @ DML = 1
Begin
-- Check C Whether the table already exists B Data rows inserted in the table , If no , Insert
If not exists (select top 1 1 from C, inserted I where c. A1 = I. A1 and C. b1 = I. B1 and C. C1 = I. C1)
Insert into C select * From inserted
End
If @ DML = 2
Begin
-- Check B Whether the table has A This row of data before table update , If , You do not need to update C Table Data , Instead A The updated row of data in the table is inserted C Table
If not exists (select top 1 from a, deleted d Where a. A1 = D. A1 and A. b1 = D. B1 and A. C1 = D. C1)
Begin
-- If C The table does not exist. B This row of updated table data , Update C Table and B The data in the row before the table update is the same.
If not exists (select top 1 1 from C, inserted I where c. A1 = I. A1 and C. b1 = I. B1 and C. C1 = I. C1)
Begin
Update C set a1 = I. a1, B1 = I. b1, C1 = I. c1 from C, inserted I, deleted d Where C. a1 = D. a1 and C. b1 = D. b1 and C. c1 = D. c1
End
-- If C Table updates B This row of data after the table , Delete C Table and B The same row of data before table update
Else
Begin
Select @ a1_d = A1, @ b1_d = b1, @ c1_d = C1 from deleted
Delete from C where @ a1_d = A1 and @ BD d = b1 and @ c1_d = C1
End

End
Else
Insert into C select * From inserted I where not exists (select 1 from C where I. a1 = C. a1 and I. b1 = C. b1 and I. c1 = C. c1)
End
If @ DML = 3
Begin
-- If A The table does not exist. B The row of data to be deleted in the table. , You need C This row of data is deleted from the table.
If not exists (select top 1 from a, deleted d Where a. A1 = D. A1 and A. b1 = D. B1 and A. C1 = D. C1)
Delete from C where exists (select 1 from deleted d Where C. A1 = D. A1 and C. b1 = D. B1 and C. C1 = D. C1)
End
Exit _: