t3 吧! 建表的时候,定义计算列 Create table #( t1 int, t2 int, t3 as (t2-t1) )
建表的时候,定义计算列 Create table #( t1 int, t2 int, t3 as (t2-t1) )
t3 吧! 建表的时候,定义计算列 Create table #( t1 int, t2 int, t3 as (t2-t1) ) 这样不行,按照你的做法,如果t2=100,t1=10,则t3会等于90,然后再更新t1=20,这时候t3会等于80,我是要做到t3=70
能不能写一个触发实现呢,当t1中数据更新时,立刻从t2中减去t1
CREATE TRIGGER trg_A_update_insert ON A FOR UPDATE,INSERT AS COMMIT TRANS UPDATE A SET t2=t2-t1 GO
UPDATE A SET t2=t2-t1
--感觉会不会递归调用触发器啊 --要防止递归调用,这样试试:create trigger tr1 on A for insert,update as begin declare @flag bit set @flag=0 select @flag=case when a.t1<>b.t1 or a.t2<>b.t2 then 1 else 0 end from inserted a,deleted b if @flag=1 begin EXEC sp_configure 'nested triggers',0 reconfigure with override update A set t2=t2-t1 EXEC sp_configure 'nested triggers',1 reconfigure with override end end
--感觉会不会递归调用触发器啊 --要防止递归调用,这样试试:create trigger tr1 on A for insert,update as begin declare @flag bit set @flag=0 select @flag=case when a.t1<>b.t1 or a.t2<>b.t2 then 1 else 0 end from inserted a,deleted b if @flag=1 begin EXEC sp_configure 'nested triggers',0 reconfigure with override update A set t2=t2-t1 EXEC sp_configure 'nested triggers',1 reconfigure with override end end
提示:触发器中不允许使用configure
刚测试了一下, EXEC sp_configure 'nested triggers',0 与 EXEC sp_configure 'nested triggers',1 结果是一样的,所以只需这样:create table A ([id]int primary key, t1 int,t2 int) insert into A select 1,1,2 union all select 2,2,3 union all select 3,3,4 union all select 4,4,5 gocreate trigger tr1 on A for insert,update as begin declare @flag bit set @flag=0 select @flag=case when a.t1<>b.t1 or a.t2<>b.t2 then 1 else 0 end from inserted a,deleted b if @flag=1 update A set t2=t2-t1 where [id] in (select [id] from inserted)end goselect * from A update A set t2=10 where [id]=1 select * from A drop table A/* (所影响的行数为 4 行)id t1 t2 ----------- ----------- ----------- 1 1 2 2 2 3 3 3 4 4 4 5(所影响的行数为 4 行) (所影响的行数为 1 行) (所影响的行数为 1 行)id t1 t2 ----------- ----------- ----------- 1 1 9 2 2 3 3 3 4 4 4 5(所影响的行数为 4 行) */
楼主注意:如果你的表A中除了主键、t1、t2还有其它字段,而你更新了其它字段,没有更新t1和t2,这时你是否要执行触发器?如果不需要执行触发器,则上面贴的代码就行了; 如果还需执行触发器,则要改一下触发器,触发器代码换成: create trigger tr1 on A for insert,update as update A set t2=t2-t1 where [id] in (select [id] from inserted)
from tablename
建表的时候,定义计算列
Create table #(
t1 int,
t2 int,
t3 as (t2-t1)
)
Create table #(
t1 int,
t2 int,
t3 as (t2-t1)
)
建表的时候,定义计算列
Create table #(
t1 int,
t2 int,
t3 as (t2-t1)
)
这样不行,按照你的做法,如果t2=100,t1=10,则t3会等于90,然后再更新t1=20,这时候t3会等于80,我是要做到t3=70
ON A
FOR UPDATE,INSERT
AS
COMMIT TRANS
UPDATE A SET t2=t2-t1
GO
--要防止递归调用,这样试试:create trigger tr1 on A
for insert,update
as
begin
declare @flag bit
set @flag=0
select @flag=case when a.t1<>b.t1 or a.t2<>b.t2 then 1 else 0 end from inserted a,deleted b
if @flag=1
begin
EXEC sp_configure 'nested triggers',0
reconfigure with override
update A set t2=t2-t1
EXEC sp_configure 'nested triggers',1
reconfigure with override
end
end
CREATE TRIGGER trg_A_update_insert
ON A
FOR UPDATE,INSERT
AS
COMMIT TRANS
UPDATE A SET t2=t2-t1
GO
提示错误:trans附近有语法错误
回复人: funsuzhou(努力ing,处变不惊!) ( ) 信誉:100 2005-11-15 13:19:00 得分: 0
--感觉会不会递归调用触发器啊
--要防止递归调用,这样试试:create trigger tr1 on A
for insert,update
as
begin
declare @flag bit
set @flag=0
select @flag=case when a.t1<>b.t1 or a.t2<>b.t2 then 1 else 0 end from inserted a,deleted b
if @flag=1
begin
EXEC sp_configure 'nested triggers',0
reconfigure with override
update A set t2=t2-t1
EXEC sp_configure 'nested triggers',1
reconfigure with override
end
end
提示:触发器中不允许使用configure
EXEC sp_configure 'nested triggers',0 与 EXEC sp_configure 'nested triggers',1
结果是一样的,所以只需这样:create table A ([id]int primary key, t1 int,t2 int)
insert into A select 1,1,2 union all
select 2,2,3 union all
select 3,3,4 union all
select 4,4,5
gocreate trigger tr1 on A
for insert,update
as
begin
declare @flag bit
set @flag=0
select @flag=case when a.t1<>b.t1 or a.t2<>b.t2 then 1 else 0 end from inserted a,deleted b
if @flag=1
update A set t2=t2-t1 where [id] in (select [id] from inserted)end
goselect * from A
update A set t2=10 where [id]=1
select * from A drop table A/*
(所影响的行数为 4 行)id t1 t2
----------- ----------- -----------
1 1 2
2 2 3
3 3 4
4 4 5(所影响的行数为 4 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)id t1 t2
----------- ----------- -----------
1 1 9
2 2 3
3 3 4
4 4 5(所影响的行数为 4 行)
*/
如果还需执行触发器,则要改一下触发器,触发器代码换成:
create trigger tr1 on A
for insert,update
as
update A set t2=t2-t1 where [id] in (select [id] from inserted)