How to create a Rigger to save updates from two tables?

Asked

Viewed 1,381 times

0

I have two tables :

  • Tabela1 : three-column id_product, product name, product price;
  • table 2 : four-column id_brand, id_product, tag, price_tag;

I created a new Tabela3 : three-column id_product, antigo_preco_product, antigo_tag

In my app I have a script php that in a given instant will make a UPDATE in the tables 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 the tables 1 and 2 in tables 3:

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 ?

php mysql sql phpmyadmin trigger

  • None of this would be necessary if prices were stored in daughter tables. tabela1_preco (id_produto, dt_ref, preço) and also for table2. Instead of your application changing the value in tables 1 and 2, a new record with the current reference date would be inserted in these new price tables.

  • @But your analogy seems interesting, could if possible give more details ?

  • I noticed that you can create tables (Tabela3), so you can change your approach of how to store prices. A price list should be MANY in relation to table produto (1.n). In your application, where you must use/show the value (both for product and brand), you will search in the table tabela1_preco using the id_produto and the dt_ref. Price research is not difficult to do (select top 1 preco from tabela1_preco where id_produto = @id_produto and dt_ref <= @data order by dt_ref desc [made in sqlserver])

  • @Islam price list does not exist, but rather a column of Tabela1 ( product table)

  • To price list you will create. Anyway, it is possible to do the way you already are (with the triggers) or remodeling. Since the question is about Rigger, I will assemble it for you and in another question you can treat how to use a modeling with a higher level of normalization.

  • @All right... I’ll wait !

Show 1 more comment

2 answers

2

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

I suggest you implement the update of the history table in the application itself. Tabela3 lacked column to record the moment the price was changed. If you need date and time, I suggest you use the data type smalldatetime. If you only need the date, you can use the data type date.

-- 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 it is necessary to place the restriction both in the history recording and in the price update. Use the same rule.

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

1

I will post the answer with Trigger (simplified).

As José Diz said, it is not possible to shoot simultaneously in a Rigger by two different tables. You must create a Rigger in each table.

Declaration by Table 3

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 consultation.

Trigger for Table 1

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 ;

For the table Trigger 2, just make the changes: change the table name and the relative fields.

I edited Sqlserver’s response to Mysql (but could not validate).

Browser other questions tagged php mysql sql phpmyadmin trigger

You are not signed in. Login or sign up in order to post.