Use Trigger to copy data from a table to another (VTiger)

Question

I'm sorry for such a newbie question, but I have two tables:

vtiger_assets

+-----------+---------+------+-----+---------+----------------+
| assetid   | account | Stat | Key | Default | assetname      |
+-----------+---------+------+-----+---------+----------------+
| 224       | int(11) | NO   | PRI | NULL    |                |
| 225       | int(11) | NO   |     | NULL    |                |
| 226       | date    | NO   |     | NULL    |                |
| 227       | date    | NO   |     | NULL    |                |
| 228       | int(11) | NO   |     | NULL    |                |
| 229       | int(11) | NO   | MUL | NULL    |                |
| 230       | int(11) | NO   | MUL | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

And vtiger_assetscf

+--------------+---------+
| assetid      | cf_658  |
+--------------+---------+
| 224          | Value 1 |
| 225          | Value 2 |
| 226          | Value 3 |
| 227          | Value 2 |
| 228          | Value 3 |
| 229          | Value 1 |
| 230          | Value 3 |
+--------------+---------+

After one day of trying and errors using Trigger, INSERT and UPDATE I give up and decided to ask the experts...

A new row is added in both tables at the same time (with a new assetid automatically added)

I need to automatically import and update (populate) the values from cf_658 column at vtiger_assetcf table to the assetname column at vtiger_assets table.

I have tried:

create trigger 'ativos' after insert on vtiger_assetscf
for each row
begin
insert into vtiger_assets (assetid, assetname) values (new.assetid, new.cf_658);
end#

I have tried a combination of INSERT and UPDATE. No luck...

Can someone help me?

score 1 · Answer 1 · answered Jan 28 '14 at 02:11

1

You have used single quotes around the name. That should cause a syntax error. Try this:

delimiter #

create trigger ativos after insert on vtiger_assetscf
for each row
begin
insert into vtiger_assets(assetid, assetname)
    values (new.assetid, new.cf_658);
end; #

answered Jan 28 '14 at 02:11

Gordon Linoff

1,242,037
58
646
786

Thanks for your answer, but I had used the quotes just for example. I have used without the quotes and get "MySQL said: #1054 - Unknown column 'assetid' in 'NEW'" But this is not the way to go... I think I'm making another mistake. A deeper one. – Guiga Figueiredo Jan 28 '14 at 22:46

score 1 · Answer 2 · answered Oct 08 '14 at 19:11

I hope you have already solved your problem. If not, I may have some questions to clarify:

1.- What do you mean with

A new row is added in both tables at the same time[...]

Is it that you use one call to the DB in which you insert both rows? Because I guess you are doing two queries on one call. One that inserts into vtiger_assets and then one that inserts into vtiger_assetscf

If it is the case, maybe you are triggering the ativostrigger before you have the row from wich to copy the data.

2.- With

with a new assetid automatically added

I supose you have your auto_incrementproperty enabled for the assetid column, but this makes me wonder, why do you have separate tables if one just copy data recently sent?

Maybe you could send one query only to vtiger_assets and have a trigger in that table (not into vtiger_assetscf) that creates a new row into vtiger_assetscf (but now I think maybe your tables are not completely shown here....)

3.- Finally: Is your assetid a key column? (if you're using auto_increment, I bet it is). If it's the case, I think the error you get is that you try to insert a row in a table with a duplicate value (the assetid).

Maybe try this instead:

create trigger `ativos` after insert on `vtiger_assetscf`
for each row
begin
update `vtiger_assets` SET `assetname` = new.`cf_658` WHERE `assetid` = NEW.`assetid`;
end#

I know It's been a while, but still...

Use Trigger to copy data from a table to another (VTiger)

2 Answers2