笔记327 触发器更新当A表某字段更新触发B表的某字段同步更新 2013-7-20

笔记327 触发器更新当A表某字段更新触发B表的某字段同步更新 2013-7-20

--触发器更新当A表某字段更新触发B表的某字段同步更新 2013-7-20
USE [tempdb]
GO



CREATE TABLE SumbitTable (sumbitnumberid INT, SumbitPictureUrlS VARCHAR(200))
GO
CREATE TABLE ContractTable  (sumbitnumber INT, SumbitNumberUrl VARCHAR(200))
GO

INSERT INTO [SumbitTable] ( [sumbitnumberid], [SumbitPictureUrlS] )
SELECT 1,'www.baidu.com' UNION ALL
SELECT 2,'www.qq.com' UNION ALL
SELECT 3,'www.163.com'

INSERT INTO [ContractTable] ( [sumbitnumber], [SumbitNumberUrl] )
SELECT 1,'www.baidu.com' UNION ALL
SELECT 2,'www.qq.com' UNION ALL
SELECT 3,'www.163.com'


SELECT * FROM [ContractTable]
SELECT * FROM [SumbitTable]




CREATE TRIGGER tgr_SumbitPic_update ON SumbitTable
    FOR UPDATE
AS
UPDATE  [ContractTable]
SET     [SumbitNumberUrl] = i.[SumbitPictureUrlS]
FROM    INSERTED AS i
INNER JOIN [ContractTable] AS c ON i.sumbitnumberid = c.sumbitnumber


-------------触发触发器
UPDATE [dbo].[SumbitTable] SET [SumbitPictureUrlS]='www.nba.com' WHERE [sumbitnumberid]=2
SELECT * FROM [ContractTable]
SELECT * FROM [SumbitTable]