Detect UPDATE with trigger

Question

Detect UPDATE with trigger

Navigation

#1 by (0 votes)

1

My table of entradas you have a field precio , the price is assigned from my other table entrada_detalles automatic by trigger with INSERT my problem is that if in table _detalles the price is modified it does not modify it in my table entrada . With a trigger DELETE it removes the price from the elimated column, that is, if it had 200 in price and I eliminate a detail that comes from that entry, it subtracts it perfect with:

update entrada a
    join entrada_detalles di
      on di.identrada = a.id
      set a.precio = a.precio - di.precio

but that is in DELETE how could I detect a UPDATE ?

mysql sql trigger

asked by DoubleM 05.05.2018 в 07:38

source

1 answer

How to use timeago (or any component vue) in Quasar page? legend of echarts always visible

score 0 · Answer 1

You can create trigger for the 3 types of events INSERT, UPDATE, DELETE

The syntax for creating a trigger is as follows

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    { BEFORE | AFTER }  { INSERT | UPDATE | DELETE }
    ON tbl_name FOR EACH ROW
    [{ FOLLOWS | PRECEDES } other_trigger_name]
    trigger_body

You can review the documentation here: link

trigger_name

trigger_name represents the name of the trigger.

{ BEFORE | AFTER }

Defines whether the trigger is executed before (BEFORE) or after the event (AFTER), on each row.

{ INSERT | UPDATE | DELETE }

Defines the event that the trigger will listen to

INSERT: Each time a row is inserted. For example with INSERT, LOAD DATA, and REPLACE.
UPDATE: Each time a row is updated with UPDATE.
DELETE: Each time a row is deleted, for example with DELETE and REPLACE.

tbl_name

tbl_name represents the name of the table the trigger will listen to.

 [{ FOLLOWS | PRECEDES } other_trigger_name]

When we have multiple triggers with the same event and the same time, FOLLOWS and PRECEDES allow us to define the order in which they will be executed.

trigger_body

trigger_body represents the body of the trigger. If we need to execute multiple statements, we must use the BEGIN ... END structure. In the body of the trigger we can access the data of the sentence with the alias NEW.columnname and with the alias OLD.columnname to the data of the row before executing the sentence.

Note: Cascades over foreign keys do not activate the triggers.