relate two tables

Question

relate two tables

Navigation

#1 by (1 votes)

0

I would like to know how I can relate two tables to different databases so that every time the value in the table bank1.dbo.table id1 will be changed also in bank2.dbo.table id1, modifying according to the row (id) which is being modified

The issue is that the tables are thus

table1
id - column2 - column3 - point
1 --- 0 ---------- 0 -------- 10
2 --- 0 ---------- 0 -------- 25

table2
id - column2 - column3 - column4 - bonus
1 ------ 0 -------- 0 ---------- 0 ---------?
2 ------ 0 -------- 0 ---------- 0 ---------?

I wanted this point to go to the bonus whenever it was changed, according to the line there (id), so in case that changed there 25 on line 2 will change here also on line 2 of the bonus column in table 2 It's kind of confusing, thanks for responding.

sql sql-server-2008

asked by anonymous 19.09.2018 / 07:55

1 answer

How to display a Menu by clicking the BottomNavigationView item Register Header on a SOAP service reference in C #

score 1 · Answer 1

In this way you should get what you want:

-- Descomentar apenas se a solução abaixo não resolver o problema
-- EXEC sp_configure 'show advanced options', 1
-- GO
-- RECONFIGURE
-- GO
-- EXEC sp_configure 'nested triggers', 0
-- GO  
-- RECONFIGURE
-- GO

-- Alterar o "MyDataBase" para o nome da base de dados
ALTER DATABASE MyDataBase SET RECURSIVE_TRIGGERS OFF
GO

CREATE TRIGGER tr_AtualizaID ON tabela1
AFTER UPDATE   
AS
    DECLARE @Id     UNIQUEIDENTIFIER
    DECLARE @Points FLOAT

    IF UPDATE(points)
    BEGIN
        SELECT  TOP 1 
                @Id     = id
            ,   @Points = points
        FROM    inserted

        UPDATE  tabela2
        SET     bonus   = @Points
            ,   origem  = 1
        WHERE   id      = @Id
    END
GO

CREATE TRIGGER tr_AtualizaID ON tabela2
AFTER UPDATE   
AS
    DECLARE @Id     UNIQUEIDENTIFIER
    DECLARE @Points FLOAT

    IF UPDATE(points)
    BEGIN
        SELECT  TOP 1 
                @Id     = id
            ,   @Points = points
        FROM    inserted

        UPDATE  tabela1
        SET     bonus   = @Points
            ,   origem  = 1
        WHERE   id      = @Id
    END
GO

Initial comments are used when uncommenting to prevent triggers nesting, but should only be used if the recursion disable solution does not work.