11,849
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
IF OBJECT_ID('trig_t_I') IS NOT NULL DROP TABLE trig_t_I
GO
CREATE TABLE t(
id INT IDENTITY(1,1) PRIMARY KEY,
A DECIMAL(10,2),
B DECIMAL(10,2),
C DECIMAL(10,2)
)
GO
-- =============================================
-- Author: yenange
-- Create date: 2018-12-20
-- Description:
-- =============================================
CREATE TRIGGER trig_t_I
ON dbo.t
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT 1 FROM INSERTED WHERE A>0.2 AND C!=B-A)
BEGIN
UPDATE t
SET C=tt.B-tt.A
FROM INSERTED AS tt
WHERE t.id=tt.id AND t.A>0.2 AND t.C!=t.B-t.A
END
END
GO
SET NOCOUNT ON
INSERT INTO t VALUES (0.3,5,2)
INSERT INTO t VALUES (0.1,4,1)
SELECT * FROM t
/*
id A B C
-- ------ ------- ---
1 0.30 5.00 4.70
2 0.10 4.00 1.00
*/