SQL Server2000 触发器修改数据范围错误,高手帮忙,多谢!
我用SQL Server2000的触发器来修改数据,即当修改表Tb_SPPrcPM的数据时,修改和它用外键对应的表Tb_SPPrc的SPPRC_AddDate字段为当前日期。但它有时能正确的修改它对应的信息,有时会把表Tb_SPPrc中所有的数据的SPPRC_AddDate都修改为当前日期。不知道为什么?各位前辈帮忙给看看,多谢了!
-----------------------------------------
触发器代码:
CREATE TRIGGER [Tri_UpdateAddDate_Tb_SPPRCPM]
ON [dbo].[Tb_SPPRCPM]
FOR UPDATE
AS
UPDATE Tb_SPPRC
SET SPPRC_AddDate = GETDATE()
FROM Tb_SPPRCPM a INNER JOIN INSERTED b
ON a.SPPRC_ID = b.SPPRC_ID
修改用的SQL语句(运行这条语句后,所有数据都给修改了):
update tb_spprcpm set spprcpm_delflag='1' where spprcpm_id in('11852','11853','11854')
[解决办法]
[color=#FF0000][/color]
CREATE TRIGGER [Tri_UpdateAddDate_Tb_SPPRCPM] ON [dbo].[Tb_SPPRCPM] FOR UPDATE AS UPDATE Tb_SPPRC SET SPPRC_AddDate = GETDATE() FROM [color=#FF0000]Tb_SPPRC[/color] a INNER JOIN INSERTED b ON a.SPPRC_ID = b.SPPRC_ID
[解决办法]
这样试试
CREATE TRIGGER [Tri_UpdateAddDate_Tb_SPPRCPM] ON [dbo].[Tb_SPPRCPM] AFTER UPDATE AS UPDATE Tb_SPPRC SET SPPRC_AddDate = GETDATE() WHERE SPPRC_ID IN (SELECT SPPRC_ID FROM INSERTED )