因为有2层的外键约束关系,所以删除userinfo的资料时,要先删除其2层外键对应表里的记录,然后再删除其本身的记录.
触发器代码如下.create trigger del_userinfo
on userinfo instead of delete
as
begin
delete from records
where account_id in
(select a.account_id
from useraccounts a
inner join deleted b on a.user_identity=b.user_identity) delete from useraccounts
where user_identity in
(select user_identity from deleted)
delete from authority
where user_identity in
(select user_identity from deleted)
delete from userinfo
where user_identity in
(select user_identity from deleted)
end
-- 测试
delete from userinfo
/*
(0 row(s) affected)(1 row(s) affected)(0 row(s) affected)(3 row(s) affected)(3 row(s) affected)
*/
-- 结果
select * from userinfoselect * from useraccounts
/*
user_identity user_name user_sex user_age user_phone user_address user_otherinfo
-------------------- ---------- -------- ----------- --------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------(0 row(s) affected)account_id account_password account_balance user_identity
-------------------- -------------------- ---------------------- --------------------(0 row(s) affected)
*/
触发器代码如下.create trigger del_userinfo
on userinfo instead of delete
as
begin
delete from records
where account_id in
(select a.account_id
from useraccounts a
inner join deleted b on a.user_identity=b.user_identity) delete from useraccounts
where user_identity in
(select user_identity from deleted)
delete from authority
where user_identity in
(select user_identity from deleted)
delete from userinfo
where user_identity in
(select user_identity from deleted)
end
-- 测试
delete from userinfo
/*
(0 row(s) affected)(1 row(s) affected)(0 row(s) affected)(3 row(s) affected)(3 row(s) affected)
*/
-- 结果
select * from userinfoselect * from useraccounts
/*
user_identity user_name user_sex user_age user_phone user_address user_otherinfo
-------------------- ---------- -------- ----------- --------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------(0 row(s) affected)account_id account_password account_balance user_identity
-------------------- -------------------- ---------------------- --------------------(0 row(s) affected)
*/
要实现数据完整性可以在DML中用事务封装来完成.
要实现数据完整性可以在DML中用事务封装来完成.
好的,我去看看资料,谢谢了