笔记327 触发器更新当A表某字段更新触发B表的某字段同步更新 2013-7-20
1 --触发器更新当A表某字段更新触发B表的某字段同步更新 2013-7-20
2 USE [tempdb]
3 GO
4
5
6
7 CREATE TABLE SumbitTable (sumbitnumberid INT, SumbitPictureUrlS VARCHAR(200))
8 GO
9 CREATE TABLE ContractTable (sumbitnumber INT, SumbitNumberUrl VARCHAR(200))
10 GO
11
12 INSERT INTO [SumbitTable] ( [sumbitnumberid], [SumbitPictureUrlS] )
13 SELECT 1,'www.baidu.com' UNION ALL
14 SELECT 2,'www.qq.com' UNION ALL
15 SELECT 3,'www.163.com'
16
17 INSERT INTO [ContractTable] ( [sumbitnumber], [SumbitNumberUrl] )
18 SELECT 1,'www.baidu.com' UNION ALL
19 SELECT 2,'www.qq.com' UNION ALL
20 SELECT 3,'www.163.com'
21
22
23 SELECT * FROM [ContractTable]
24 SELECT * FROM [SumbitTable]
25
26
27
28
29 CREATE TRIGGER tgr_SumbitPic_update ON SumbitTable
30 FOR UPDATE
31 AS
32 UPDATE [ContractTable]
33 SET [SumbitNumberUrl] = i.[SumbitPictureUrlS]
34 FROM INSERTED AS i
35 INNER JOIN [ContractTable] AS c ON i.sumbitnumberid = c.sumbitnumber
36
37
38 -------------触发触发器
39 UPDATE [dbo].[SumbitTable] SET [SumbitPictureUrlS]='www.nba.com' WHERE [sumbitnumberid]=2
40 SELECT * FROM [ContractTable]
41 SELECT * FROM [SumbitTable]
