How to create a trigger to save updates of two tables?

Navigation
0

I have two tables:

  • table1 : with three columns product_id , product_name , product_price ;
  • table2 : with four columns tag_name , product_id , tag_name , tag_name li>

I've created a new table3 : with three columns product_id , old_product , old_post_name

In my application I have a php script that will make a UPDATE in the 1 and 2 precisely in the columns preco_produto and preco_marca .

I would like to create a TRIGGER in order to save the modified data in tables 1 and 2 in tables3 :

MY LOGIC:

CRIE UM TRIGGER ANTES DE UPDATE NA TABELA 1 e 2 GUARDE OS ANTIGOS VALORES NA TABELA3

PROBLEM:

How to declare the two tables in trigger ?

    
asked by anonymous 16.12.2016 / 12:46

2 answers

1

André, it is not possible to associate a trigger procedure with more than one table. Only one table.

I suggest that you implement the history table update in the application itself. Table3 was missing column to record when the price was changed. If you need a date and time, I suggest you use the smalldatetime data type. If you only need the date, you can use the date data type. 

-- código #1
BEGIN TRANSACTION;
declare @Hoje smalldatetime;
set @Hoje= Cast(Current_timestamp as smalldatetime);

-- acrescenta valores atuais na tabela de histórico de preços
INSERT into tabela3 (id_produto, data, antigo_preco_produto, antigo_preco_marca)
  SELECT T1.id_produto, @Hoje, T1.preço_produto, T2.preço_marca
    from tabela1 as T1 
         inner join tabela2 as T2 on T1.id_produto = T2.id_produto
    where _____;

-- atualiza tabela1
UPDATE tabela1
  set preço_produto= ...
  where _____;

-- atualiza tabela2
UPDATE tabela2
  set preço_marca= ...
  where _____;

COMMIT;

In the above code you need to put the restriction both on history recording and on price maintenance. Use the same rule.

Note that the code is encapsulated by a BEGIN TRANSACTION / COMMIT pair. It is necessary to ensure the consistency of the data.

    
16.12.2016 / 15:15
1

I'll post the response with the (simplified) trigger.

As José Diz said, it is not possible to simultaneously trigger in a trigger by two different tables. You should create a trigger on each table.

Statement of Table3 

CREATE TABLE TABELA3 ('DATA_REF' DATETIME NOT NULL, 'ID_PRODUTO' INT NOT NULL, 'ANTIGO_PRECO_PRODUTO' NUMERIC(15,2), 'ANTIGO_PRECO_MARCA' NUMERIC(15,2))

The date field is important for future reference.

Trigger for Table1 

DELIMITER $$

DROP TRIGGER 'TABELA1_PRODUTO '$$

CREATE TRIGGER TABELA1_PRODUTO AFTER UPDATE ON TABELA1
FOR EACH ROW
BEGIN
    IF (NEW.PRECO_PRODUTO != OLD.PRECO_PRODUTO) THEN
        INSERT INTO TABELA3
            ('DATA_REF', 'ID_PRODUTO', 'ANTIGO_PRECO_PRODUTO')
        VALUES
            (NOW(), NEW.ID_PRODUTO, OLD.PRECO_PRODUTO);
    END IF;
END$$
DELIMITER ;

To trigger table2, just make the changes: change the table name and relative fields.

I edited the response from SQLServer to MySql (but I could not validate).

    
16.12.2016 / 15:31
JSON manipulation Need for SQLite [closed]