如题:最近刚开始研究触发器的例子!首先做了个2个表关联的例子正确,但是3个表就错误了!
先是2个表时
省表
Provinceid Provincename
1 黑龙江
2 吉林
市表
cityid cityname Provinceid
1 哈尔滨 1
2 牡丹江 1
这样时删除省表Provinceid=1时 市表Provinceid=1也删除了
后来我又加了个
县表County
Countyid Countyname cityid
1 尚志 1
2 阿城 1
现在如果删除市表cityid=1县表cityid=1也删除 但是如果删除省表Provinceid=1时提示约束错误,但如果市表中只有一个属于省表的数据就正确,所以我估计是删除市表中省表Provinceid=1时数据不完全
我的省表触发器代码是这样的:CREATE TRIGGER [dbo].[Province_del]
ON [dbo].[Province]
Instead Of DELETE
AS
BEGIN
declare @ID bigintSelect @ID=Provinceid from Deleted
Delete From city where Provinceid=@ID
Delete From Province where Provinceid=@ID
END
市表CREATE TRIGGER [dbo].[city_del]
ON [dbo].[city]
Instead Of DELETE
AS
BEGIN
declare @ID bigint
Select @ID=cityid from Deleted
Delete From County where cityid=@ID
Delete From city where cityid=@ID
END
先是2个表时
省表
Provinceid Provincename
1 黑龙江
2 吉林
市表
cityid cityname Provinceid
1 哈尔滨 1
2 牡丹江 1
这样时删除省表Provinceid=1时 市表Provinceid=1也删除了
后来我又加了个
县表County
Countyid Countyname cityid
1 尚志 1
2 阿城 1
现在如果删除市表cityid=1县表cityid=1也删除 但是如果删除省表Provinceid=1时提示约束错误,但如果市表中只有一个属于省表的数据就正确,所以我估计是删除市表中省表Provinceid=1时数据不完全
我的省表触发器代码是这样的:CREATE TRIGGER [dbo].[Province_del]
ON [dbo].[Province]
Instead Of DELETE
AS
BEGIN
declare @ID bigintSelect @ID=Provinceid from Deleted
Delete From city where Provinceid=@ID
Delete From Province where Provinceid=@ID
END
市表CREATE TRIGGER [dbo].[city_del]
ON [dbo].[city]
Instead Of DELETE
AS
BEGIN
declare @ID bigint
Select @ID=cityid from Deleted
Delete From County where cityid=@ID
Delete From city where cityid=@ID
END
SQL Server中的触发器与Oracle数据库中的触发器不同。
SQL Server中的触发器不是逐条执行的。
如果定义一个游标遍历Deleted表,应该就没错了。
或者用其他方式遍历也可以。