34,576
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE t1(ID INT IDENTITY(1,1), a INT,b INT )
go
INSERT INTO t1(a,b)
SELECT ISNULL(MAX(a)+1,1),0 FROM t1
GO 10
CREATE TRIGGER trg_Update_t1_B ON T1 FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE t1
SET b = CASE WHEN i.a!=d.a THEN d.a ELSE t1.b END
FROM t1
INNER JOIN inserted AS i
ON i.ID = t1.ID
INNER JOIN deleted AS d
ON i.ID = d.ID;
END
GO
SELECT * from t1
UPDATE t1 SET t1.a=t1.a*2 WHERE ID<6
SELECT * from t1
--更新前:
ID a b
----------- ----------- -----------
1 1 0
2 2 0
3 3 0
4 4 0
5 5 0
6 6 0
7 7 0
8 8 0
9 9 0
10 10 0
更新后:
ID a b
----------- ----------- -----------
1 2 1
2 4 2
3 6 3
4 8 4
5 10 5
6 6 0
7 7 0
8 8 0
9 9 0
10 10 0
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([a] int,[b] INT)
Insert #T1
select 1,0 union all
select 2,0 union all
select 3,0 union all
select 4,0
Go
Create table #T2([a] int,[b] INT)
--测试数据结束
DECLARE @i INT = 2 --a=1的要值要改成2
UPDATE #T1 SET b=a,a=@i WHERE a=1
SELECT * FROM #T1
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([a] int,[b] INT)
Insert #T1
select 1,0 union all
select 2,0 union all
select 3,0 union all
select 4,0
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([a] int,[b] INT)
--测试数据结束
DECLARE @i INT = 2 --a=1的要值要改成2
INSERT INTO #T2
( a, b )
VALUES ( @i, -- a - int
(SELECT a FROM #T1 WHERE a=1) -- b - int
)
UPDATE #T1 SET a=@i WHERE a=1
SELECT * FROM #T1
SELECT * FROM #T2