当然是用触发器。eg:新增触发器
create trigger 名1 on t_detail
for insert
as
insert t_main
select d_id,d_name,d_des from inserted
where d_id not in (select d_id from t_main)
go
eg:删除触发器
create trigger 名2 on t_detail
for insert
as
delete t_main
where d_id in (select d_id from deleted)
eg:修改触发器
create trigger 名2 on t_detail
for update
as
update a set a.d_name = b.d_name,a.d_des = b.d_des
from t_main a
join inserted b on a.d_id = b.d_id
create trigger 名1 on t_detail
for insert
as
insert t_main
select d_id,d_name,d_des from inserted
where d_id not in (select d_id from t_main)
go
eg:删除触发器
create trigger 名2 on t_detail
for insert
as
delete t_main
where d_id in (select d_id from deleted)
eg:修改触发器
create trigger 名2 on t_detail
for update
as
update a set a.d_name = b.d_name,a.d_des = b.d_des
from t_main a
join inserted b on a.d_id = b.d_id
CREATE TRIGGER [TRIGGER NAME] ON [dbo].[T_Detail]
FOR INSERT
AS
insert into t_main(d_ID ,d_Name,d_des ) select distinct d_id,d_name,d_des from inserted
create table T_Main(
d_ID varchar(16) null,
d_Name varchar(50) null,
d_des varchar(100) null)create table T_Detail(
d_ID varchar(16) not null primary key,
d_Name varchar(50) not null,
d_des varchar(100) null,
d_f1 varchar(20) null,
d_f2 varchar(20) null,
d_f3 varchar(20) null)
go--T_Detail中增删除记录时,同步T_Main的触发器
create trigger t_process on T_Detail
for insert,update,delete
as
begin tran
delete T_Main
from T_Main a join deleted b on a.d_ID=b.d_ID
if @@error<>0 goto lb_err insert into T_Main(d_ID,d_Name,d_des)
select d_ID,d_Name,d_des from inserted
if @@error=0
commit tran
else goto lb_err
lb_err:
rollback tran
go
在记录更新前.开启事务
并用错误处理捕获错误.如果出错,回滚事务.
如果所有处理正确完成,提交事务.因为这样工作,触发器中的事务为内层事务
基于事务的嵌套处理规则.可以满足楼主的要求
create table t_main(d_ID varchar(16) null,
d_Name varchar(50) null,
d_des varchar(100) null
)
go
create table t_detail(d_ID varchar(16) not null primary key,
d_Name varchar(50) not null ,
d_des varchar(100) null,
d_f1 varchar(20) null,
d_f2 varchar(20) null,
d_f3 varchar(20) null
)
go
create trigger 名 on t_detail
for insert
as
insert t_main
select d_id,d_name,d_des from inserted
where d_id not in (select d_id from t_main)
go
insert t_detail values('001','focus','setfocus','中国','中华人民共和国','中国人民')
/* 没有用insert语句往t_main中加记录,以下显示结果 */
select * from t_main
/*
d_ID d_Name d_des
---------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------
001 focus setfocus(所影响的行数为 1 行)*/
比如说id这个参数在insert之前输入
剩下的参数有触发器来自动增加!