CODEWITHSUNDEEP
mysqlsqltriggersphpmyadmin
trinny
trinny

Reputation: 232

SQL TRIGGER From one table to another when update

I'll start that I am using mysql in phpmyadmin.

My tables:

Table1:

primary key = id1;
cash1 = thing I want to pick from that table;

Table2:

primary key = id2;

cash2 = thing I want to pick from that table;

Table3:

foreign key1 = id1;
foreign key2 = id2;
cash3 = thing I want to make;

So, I want to make:

Update (or insert into?) cash3 = cash1*cash2/100 when UPDATE ON cash1 or cash2.

Tried many things, nothing seems to work...

Upvotes: 1

Views: 166

Answers (1)

LuigiEdlCarno
LuigiEdlCarno

Reputation: 2415

Your triggers (you need one for each table1 and table2) should look something like this:

create trigger cash1 on table1 for insert, update
   Select @c1=sum(cash) from table1
   Select @c2=sum(cash) from table2
   Update table3 set cash=@c1*@c2/100
end

Note: The above is just pseudo code, as I am not familiar with the mysql syntax.

What this trigger does is, when ever you change the amount of money in table1, it selects the money from table1 and table2 and calculates the amount for table3 and updates it.

You need another trigger, that does the same on table2.

It is hard for use to give you a decent code examplke without knowing your table setup (column names)

Hope this helps.

Upvotes: 1

Related Questions