TRIGGER for INSERT/ UPDATE from table source for table destination

  • I have a database with normalized tables and another application with non-normalized table.
    So I need to create a trigger for when I insert / update a register in the source table (normalized)
    I can insert some of these records into a table in the destination (non-normalized).

    I want to ask colleagues for help if there is any better way to do this. Or if I'm on the right track.

    Initially I created a trigger in which I test if there is any update, otherwise I do INSERT in the destination table (non normalized)

    CREATE TRIGGER [dbo].[TriggerUpdateInsert] ON [dbo].[TABLE_SOURCE]
    AFTER INSERT, UPDATE
    AS

    DECLARE @QtdRows INT

    BEGIN
    UPDATE [BANK_TANK]..[TABLE_DESTINATION]
    SET FIELD_1 = TABLE_SOURCE.FIELD_1
    WHERE
      TABLE_SOURCE.FIELD_KEY = TABLE_DESTINATION.FIELD_KEY
    SET @QtdRows = @@ROWCOUNT

    UPDATE [BANCO_TANK]..

    SET FIELD_2 = TABLE_SOURCE.FIELD_2
    WHERE
      TABELA_SOURCE.FIELD_KEY = TABLE_DESTINATION.FIELD_KEY
    SET @QtdRows = @@ROWCOUNT

    -- if UPDATE did not find one register, I INSERT new register in table destination.
    IF ( @QtdRows = 0 )
     BEGIN            
      INSERT INTO [BANCO_TANK]..[TABLE_DESTINATION]
      SELECT
        FIELD_1,
        FIELD_2,
      FROM TABLE_SOURCE1
      LEFT JOIN TABLE_SOURCE2 ON
       TABLE_SOURCE1.FIELD_KEY = TABLE_SOURCE2.FIELD_KEY
     END

    Thank you

  • What about DELETEs?
    Any chance of just using a View in database BANCO_TANK that points to TABLE_SOURCE?

  • C DBA Lockhart - Thursday, October 25, 2018 2:02 PM

    What about DELETEs?
    Any chance of just using a View in database BANCO_TANK that points to TABLE_SOURCE?

    Lockhart 
    Thanks for your feedback, but there is already an old application database. Where this data is worked locally as a BI. So I think in this case the view is not a good idea. about delete is not necessary.

    If you have another idea please share again!

    Thank very much


  • SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TRIGGER [dbo].[TriggerUpdateInsert]
    ON [dbo].[TABLE_SOURCE]
    AFTER INSERT, UPDATE
    AS
    SET NOCOUNT ON;

    UPDATE TD
    SET FIELD_1 = i.FIELD_1,
        FIELD_2 = i.FIELD_2
    FROM [BANK_TANK].dbo.[TABLE_DESTINATION] TD
    INNER JOIN inserted i ON i.FIELD_KEY = TD.FIELD_KEY

    -- INSERT new register row in table_destination if one doesn't already exist.
    INSERT INTO [BANCO_TANK].dbo.[TABLE_DESTINATION] ( FIELD_KEY, FIELD_1, FIELD_2 )
    SELECT i.FIELD_KEY, i.FIELD_1, i.FIELD_2
    FROM inserted i
    WHERE NOT EXISTS (
        SELECT 1
        FROM [BANCO_TANK].dbo.[TABLE_DESTINATION] TD
        WHERE TD.FIELD_KEY = i.FIELD_KEY
    )
    /* end of trigger */
    GO

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Guru Thank you for feedback,
    Thank you for your feedback. I liked. But an observation in this case, if I have three fields and update only one, the other two will be overwritten.
    For this case I do

        -- If There is Update Field_1 
        IF    UPDATE (Field_1)
            BEGIN
                UPDATE [BANK_DESTINATION]..[TABLE_Destination]
                SET
                    TD.[Field_1] = I.[Field_1]
                FROM [BANK_DESTINATION]..TABLE_Destination TD
                    INNER JOIN INSERTED I ON
                         TD.[Field.Key] = I.[Field.Key]     
            END
            SET  @qtdeRows = @@rowcount

        -- If There is Update Field_2 
        IF    UPDATE (Field_1)
            BEGIN
                UPDATE [BANK_DESTINATION]..[TABLE_Destination]
                SET
                    TD.[Field_1] = I.[Field_1]
                FROM [BANK_DESTINATION]..TABLE_Destination TD
                    INNER JOIN INSERTED I ON
                         TD.[Field.Key] = I.[Field.Key]     
            END
            SET  @qtdeRows = @@rowcount

      IF (@qtdeRows = 0 )
            BEGIN        
             INSERT INTO [TANK_FEDB]..[MWIN_TANK100_PARTS]

    What your opinion ?

    Thank you.

  • antoniop.silv - Friday, October 26, 2018 11:49 AM

    Guru Thank you for feedback,
    Thank you for your feedback. I liked. But an observation in this case, if I have three fields and update only one, the other two will be overwritten.
    For this case I do

        -- If There is Update Field_1 
        IF    UPDATE (Field_1)
            BEGIN
                UPDATE [BANK_DESTINATION]..[TABLE_Destination]
                SET
                    TD.[Field_1] = I.[Field_1]
                FROM [BANK_DESTINATION]..TABLE_Destination TD
                    INNER JOIN INSERTED I ON
                         TD.[Field.Key] = I.[Field.Key]     
            END
            SET  @qtdeRows = @@rowcount

        -- If There is Update Field_2 
        IF    UPDATE (Field_1)
            BEGIN
                UPDATE [BANK_DESTINATION]..[TABLE_Destination]
                SET
                    TD.[Field_1] = I.[Field_1]
                FROM [BANK_DESTINATION]..TABLE_Destination TD
                    INNER JOIN INSERTED I ON
                         TD.[Field.Key] = I.[Field.Key]     
            END
            SET  @qtdeRows = @@rowcount

      IF (@qtdeRows = 0 )
            BEGIN        
             INSERT INTO [TANK_FEDB]..[MWIN_TANK100_PARTS]

    What your opinion ?

    Thank you.


    -- If There is Update Field_1 or Field_2
    IF UPDATE(Field_1) OR UPDATE(Field_2)
    BEGIN
        UPDATE [BANK_DESTINATION]..[TABLE_Destination]
        SET
        TD.[Field_1] = CASE WHEN UPDATE(Field_1) THEN I.[Field_1] ELSE TD.[Field_1] END,
        TD.[Field_2] = CASE WHEN UPDATE(Field_2) THEN I.[Field_2] ELSE TD.[Field_2] END
        FROM [BANK_DESTINATION]..TABLE_Destination TD
        INNER JOIN INSERTED I ON
        TD.[Field.Key] = I.[Field.Key]
    END /*IF*/
    SET @qtdeRows = @@rowcount
    ...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thank you my friend.

    You are the guy.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply