创建触发器以更新同一表中的多个行/记录 [英] Create a Trigger to update multiple rows / records in the same table

问题描述

2019年3月4日

我想在我正在使用的数据库表中创建一个触发器。 在我的职业生涯中，我没有达到这一点，所以写了一个触发器。 我想在更新相关记录时更新表格中的其他记录。 请允许我解释一下我想要做什么
。

I would like to create a trigger in a database table I am working with.  I have not up to this point in my career written a trigger.  I would like to update other records in a table when a related record is updated.  Allow me to explain what I am wanting to do.

表：TABLE1

受影响的字段：CompanyNumber，Rate1，Rate2

Fields affected: CompanyNumber, Rate1, Rate2

场景：我希望能够在一个CompanyNumber下更新Rate1或Rate2，然后更新其他公司编号中的Rate1或Rate2。

Scenario: I would like to be able to update Rate1 or Rate2 under one CompanyNumber and then update Rate1 or Rate2 in the other company numbers.

示例1：我更新Rate1，其中CompanyNumber = 5. 我想更新Rate1，其中CompanyNumber在（1,2,3,4）。

Example1: I update Rate1 where CompanyNumber=5.  I would like to update Rate1 where CompanyNumber in (1,2,3,4).

示例2：我更新Rate2，其中CompanyNumber = 2. 我想更新Rate2，其中CompanyNumber在（1,3,4,5）。

Example2: I update Rate2 where CompanyNumber=2.  I would like to update Rate2 where CompanyNumber in (1,3,4,5).

示例3：我更新Rate1和Rate2，其中CompanyNumber = 3. 我想更新Rate1和Rate2，其中CompanyNumber在（1,2,4,5）

Example3: I update Rate1 and Rate2 where CompanyNumber=3.  I would like to update Rate1 and Rate2 where CompanyNumber in (1,2,4,5)

你应该知道我在寻找什么。

You should have the idea of what I am looking for.

推荐答案

DROP TABLE IF EXISTS Table1;
GO

CREATE TABLE Table1 (
	CompanyNumber int,
	Rate1 int, 
	Rate2 int
);

INSERT INTO Table1 VALUES
(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5);
GO

CREATE TRIGGER trg_Table1_Update
ON Table1
INSTEAD OF UPDATE
AS
BEGIN
	DECLARE @companyNumber int;
	DECLARE @rate1 int;
	DECLARE @rate2 int;

	SELECT @companyNumber = CompanyNumber, @rate1 = Rate1, @rate2 = Rate2
	FROM inserted

	IF UPDATE(Rate1) AND UPDATE(Rate2)
	BEGIN
		IF @companyNumber = 3
		BEGIN
			UPDATE Table1
			SET Rate1 = @rate1, Rate2 = @rate2
			FROM Table1
			WHERE CompanyNumber IN (1, 2, 3, 4, 5);
		END
	END
	ELSE IF UPDATE(Rate1)
	BEGIN
		IF @companyNumber = 5
		BEGIN
			UPDATE Table1
			SET Rate1 = @rate1
			FROM Table1
			WHERE CompanyNumber IN (1, 2, 3, 4, 5);
		END
	END
	ELSE IF UPDATE(Rate2)
	BEGIN
		IF @companyNumber = 2
		BEGIN
			UPDATE Table1
			SET Rate2 = @rate2
			FROM Table1
			WHERE CompanyNumber IN (1, 3, 3, 4, 5);
		END
	END
END
GO

/*
UPDATE Table1 SET Rate1 = 6 WHERE CompanyNumber = 5;
GO
SELECT * FROM Table1;
GO

UPDATE Table1 SET Rate1 = 7 WHERE CompanyNumber = 2;
GO
SELECT * FROM Table1;
GO

UPDATE Table1 SET Rate1 = 8, Rate2 = 9 WHERE CompanyNumber = 3;
GO
SELECT * FROM Table1;
GO
*/

这篇关于创建触发器以更新同一表中的多个行/记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！