表a
编号 值 参数编号
1 aaa 1130101012 bbb 113110102
表b
编号 值 参数编号 room data
1 aaa 10101 101 01
2 bbb 10102 101 02
我想在A表建个触发器,当A表插入数据时时,B表也插入相同的数据,并且b表的后两个字段是A表的”参数编号“字段截取的后
的数值(room 是后五位中的前三位 data 是后五位中的末两位)Create trigger HisData_insert on Homs_datacenter.dbo.HistoryData
for insert,UPDATE
as
DECLARE @DataId int
insert Hcms.dbo.HistoryData(@DataId=DataId,OccurTime,DataValue,Isproessed) select DataId,OccurTime,DataValue,Isproessed from inserted
IF (@DataId <> '')
BEGIN
update Hcms.dbo.HistoryData set room = (@dataId.SUBSTRING[0,-3])
END
编号 值 参数编号
1 aaa 1130101012 bbb 113110102
表b
编号 值 参数编号 room data
1 aaa 10101 101 01
2 bbb 10102 101 02
我想在A表建个触发器,当A表插入数据时时,B表也插入相同的数据,并且b表的后两个字段是A表的”参数编号“字段截取的后
的数值(room 是后五位中的前三位 data 是后五位中的末两位)Create trigger HisData_insert on Homs_datacenter.dbo.HistoryData
for insert,UPDATE
as
DECLARE @DataId int
insert Hcms.dbo.HistoryData(@DataId=DataId,OccurTime,DataValue,Isproessed) select DataId,OccurTime,DataValue,Isproessed from inserted
IF (@DataId <> '')
BEGIN
update Hcms.dbo.HistoryData set room = (@dataId.SUBSTRING[0,-3])
END
ON [dbo].[B]
for INSERT,DELETE,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
insert A([Count]) select Id from inserted
-- Insert statements for trigger hereEND
for insert,UPDATE,delete
as
begin
insert b
select 编号,值,right(参数编号,5),left(right(参数编号,5),3),right(参数编号,2)
from inserted
delete b from b , deleted d where d.编号=b.编号
end
go