如果id为自动递增 则在维护时要用到
set identity_insert [dbo].表名 on --关闭自动递增
set identity_insert [dbo].表名 off --取消自动递增
set identity_insert [dbo].表名 on --关闭自动递增
set identity_insert [dbo].表名 off --取消自动递增
解决方案 »
- SQL SVERVER 2008 查询编辑器中的IntelliSense功能不能用了
- 请教一个电子商务数据库设计的问题,请指教
- 求一简单的sql语句,在线等
- sql server2005 怎样连接到sql server2000?
- 数据库异地备份的问题- 邹建老大请进
- 报表统计的问题,请高手帮忙!
- 急,建了个触发器,导致服务器连不上了。请帮忙
- 如何使select * from sam语句跳过前面100行,从101行开始获取?
- 一个关于两个表之间的查询问题
- 请问高手怎么通过日志来恢复数据文件啊?
- 请大虾支招,关于sqlserver中删除数据库
- 如何对存储过程或者动态查询语句的返回的结果集进行操作?在不允许使用临时表的前提下!高分求解
After Insert
set identity_insert [dbo].表名 on --关闭自动递增
insert into b
select * from inserted
set identity_insert [dbo].表名 off --取消自动递增
After Update
Update b set 字段=t.,.....Where id = --用id作为主键更新
Create Trigger tr_Delete_A
After Delete
Delete From B Where id = 用id作为主键更新然后,在b也创建同样的触发器就可以了,其实同步的关键是在insert时考虑id自动增长
for Insert
as
set nocount on
set identity_insert B on /*关闭自动递增*/
insert into B([id],f1,f2,...) select * from inserted
set identity_insert B off /*取消自动递增*/
set nocount offCreate Trigger tr_del On A
for delete
as
declare @id int
set nocount on
select @id=[id] from deleted
delete from B where [id]=@id
set nocount offCreate Trigger tr_update On A
for update
as
declare @id int
set nocount on
select @id=[id] from deleted
delete from B where [id]=@id
set identity_insert B on /*关闭自动递增*/
insert into B([id],f1,f2,...) select * from inserted
set identity_insert B off /*取消自动递增*/
set nocount off
b表上的触发器安装下面的两个自己改一下,下面两个只是针对a表的变动来改动b表的create trigger auto_insert_delete on a
for insert,delete
as
set identity_insert b on
insert into b (id,user_name)
select id,user_name from inserted
set identity_insert b off
delete b from b join deleted a on b.id = a.id
go
create trigger auto_update on a
for update
as
update b set user_name = a.user_name from inserted a join b on a.id = b.id
go
对B表进行增删改.A表也要增删改.----------------------------这样不行的,会产生嵌套的哦~~~~
CREATE Trigger a1 on [dbo].[a]
for Insert
as
set nocount on
set identity_insert [dbo].[b] on /*关闭自动递增*/
insert into [dbo].[b]([id],user_name) select * from inserted
set identity_insert [dbo].[b] off /*取消自动递增*/
set nocount off
----------------
b表作的触发器:
CREATE Trigger a1 on [dbo].[b]
for Insert
as
set nocount on
set identity_insert [dbo].[a] on /*关闭自动递增*/
insert into [dbo].[a]([id],user_name) select * from inserted
set identity_insert [dbo].[a] off /*取消自动递增*/
set nocount off
---------------------------
产生错误。原因嵌套大于32。现在是想消除嵌套。
我想要实现的是:
当对A操作是,只触发B。就停止而不让B又反过来触发A
当对B操作是,只触发A。就停止而不让A又反过来触发B
因为A,B表是由不同和程序进行单独的操作!
reconfigure
可以取消嵌套
CREATE Trigger a1 on [dbo].[a]
for Insert
as
alter table b DISABLE trigger all ---在所有的出发器加此语句
set nocount on
set identity_insert [dbo].[b] on /*关闭自动递增*/
insert into [dbo].[b]([id],user_name) select * from inserted
set identity_insert [dbo].[b] off /*取消自动递增*/
set nocount off
alter table b ENABLE trigger all ---在所有的出发器加此语句
for update,insert,delete
as
alter table t_b DISABLE TRIGGER tr_b
----------------------------------
if not exists (select * from deleted) --插入
insert t_b(username) select username from inserted
else if not exists (select * from inserted)--删除
delete t_b where id in (select id from deleted)
else--更新
update a set a.username=i.username from t_b as a,inserted as i where a.id=i.id
alter table t_b ENABLE TRIGGER tr_b------------------------------------------
create trigger tr_b on t_b
for update,insert,delete
as
alter table t_a DISABLE TRIGGER tr_a
----------------------------------
if not exists (select * from deleted) --插入
insert t_a(username) select username from inserted
else if not exists (select * from inserted) --删除
delete t_a where id in (select id from deleted)
else --更新
update a set a.username=i.username from t_a as a,inserted as i where a.id=i.id
alter table t_a ENABLE TRIGGER tr_a
select * from t_a
select * from t_b--测试:
insert t_a(username) values('test')
insert t_b(username) values('test2')
update t_a set username='test1' where id=1
update t_b set username='test3' where id=1
delete from t_a where id=1
delete from t_b where id=2
--------------------------
select * from t_a
select * from t_b