开发记录——触发器实现 数据库关联字段
数据库关联字段
当字段user_autoid修改的时候,我希望对象的另一个表的字段zs_schoolinfo也跟着修改
使用触发器code,当两个表的字段内容完全相同时,使用sql外键也可以实现
Create Trigger schoolID
on zs_useraccount
for INSERT,UPDATE,DELETE
NOT FOR REPLICATION
as
set nocount on
if(select count(*) from DELETED)>0
begin
delete zs_schoolinfo where schoolid in(select user_autoid from deleted)
end
if(select count(*) from inserted)>0
begin
insert zs_schoolinfo(schoolid) select schoolid=user_autoid from inserted
end
同时更新数据库多个字段
Create Trigger schoolID_celtype1
on zs_useraccount
for INSERT,UPDATE,DELETE
NOT FOR REPLICATION
as
set nocount on
if(select count(*) from DELETED)>0
begin
delete zs_schoolinfo where schoolid in(select user_autoid from deleted)
end
if(select count(*) from inserted)>0
begin
insert zs_schoolinfo(schoolid,schooltypeid,schoolname)
select user_autoid,celtype,name from inserted
end