这个触发器如何写啊，急，100分在线等

[pureblue007]

要经常插入数据到表a
citycode varchar(20),
cityname varchar(20),
provincename varchar(20)

表b的数据是固定的
provinceid int
citycode varchar(20),
cityname varchar(20),
provincename varchar(20)

当插入citycode数据到表a的时候，cityname和prvincename两个字段的值想从表B中查询到，插入进去。
这个触发器该怎么写呢？多谢了，急在线等

[paoluo]

Create Trigger [InsteadInsert] On a
Instead Of Insert
AS
Insert A Select T1.citycode,T2.cityname,T2.provincename from Inserted T1 Inner Join B T2 On T1.citycode=T2.citycode
GO

[Comer]

CREATE TRIGGER [trtest] ON [dbo].[a]
FOR INSERT
AS
declare @citycode varchar(20)
declare @cityname varchar(20)
declare @provincename varchar(20)

select @citycode = citycode from inserted

select @cityname = cityname, @provincename = provincename from b where citycode = @citycode

update a set cityname = @cityname, provincename = @provincename where citycode = @citycode

[paoluo]

--建立测试环境
Create Table A
(citycode varchar(20),
cityname Nvarchar(20),
provincename Nvarchar(20))

Create Table B
(provinceid int,
citycode varchar(20),
cityname Nvarchar(20),
provincename Nvarchar(20))
--插入数据
Insert B Values(1,'01',N'广州',N'广东')
GO
--建立触发器
Create Trigger [InsteadInsert] On a
Instead Of Insert
AS
Insert A Select T1.citycode,T2.cityname,T2.provincename from Inserted T1 Inner Join B T2 On T1.citycode=T2.citycode
GO
--测试
Insert A Values('01',Null,Null)
Select * from A
--删除测试环境
Drop table A,B
--结果
/*
citycode cityname provincename
01 广州 广东
*/

[Comer]

鱼兄果然高

佩服

[paoluo]

二楼的触发器是不行的，除非一次只往A表中插入一条数据。