SQL Server What is the proper way to compute attributes in a table based on other attributes in other table?

alen0pnh 于 9个月前发布在其他

关注(0)|答案(1)|浏览(87)

Lets have for example two tables: Table A and Table B which are in JOIN.

Table A:

id INT PK
attr1 INT

Table B:

id INT PK
table_A_id INT NOT NULL FK
attr1 INT
attr2 INT

Table Battr2 is computed as TableB.attr2 = TableA.attr1 * TableB.attr1 .

The requirement is the following:

If a new record is inserted into Table B or an existing record is updated where the Table Battr1 changes the Table Battr2 must be calculated/recalculated.
If Table Aattr1 is changed then corresponding Table Battr2 should be recalculated.

So I am dealing with dependency between attributes in computation.

What is the proper way to perform the calculations in MSSql? Maybe triggers?

sql-server

来源：https://stackoverflow.com/questions/77574217/what-is-the-proper-way-to-compute-attributes-in-a-table-based-on-other-attribute

1条答案

按热度按时间

fhg3lkii1#

In SQL Server, you can use triggers to automatically perform calculations or actions when certain events occur, such as the insertion or update of records. For your specific case, you can use an AFTER INSERT, UPDATE trigger on TableB to recalculate attr2 based on the conditions you provided.

Example code:

CREATE TRIGGER tr_TableB_UpdateAttr2
ON TableB
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF UPDATE(attr1) OR UPDATE(attr2)
    BEGIN
        UPDATE b
        SET attr2 = a.attr1 * b.attr1
        FROM TableB b
        INNER JOIN inserted i ON b.id = i.id
        INNER JOIN TableA a ON b.table_A_id = a.id;
    END
END;

赞(0）回复(0）举报 9个月前

我来回答

SQL Server What is the proper way to compute attributes in a table based on other attributes in other table?

1条答案

相关问题

热门标签

最新问答