Triggers with Liferay

[Victor Skovorodnikov, modificado 12 Anos atrás.]

Hi everyone,

I hope somebody could help me with the problem we have. Our current Liferay version is 5 and we synchronize Liferay's MySQL database and PostreSQL. When Liferay user updates their address, we run synchronization and it updates address in PostgreSQL. We go by modifiedDate field to compare dates between PostgreSQL and MySQL. I think there's something fishy going on with modifiedDate because it changes not only when someone updates, say, Phone, but it also changes when user simply logs into our Liferay portal.

I noticed this when I applied triggers, here's a complete record of my commands:

mysql> use lportal;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show triggers;
+---------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+
| Trigger | Event  | Table   | Statement                                                                                                                                 | Timing | Created | sql_mode | Definer        |
+---------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+
| myt     | UPDATE | Address | begin insert into address_update values (NEW.userId, NEW.street1, NEW.city, NEW.zip, NEW.regionId, NEW.countryId, NEW.modifiedDate) ; end | BEFORE | NULL    |          | root@localhost | 
| mephone | UPDATE | Phone   | begin  insert into phone_update values(NEW.userId, NEW.number_, NEW.modifiedDate, NEW.typeId); end                                        | BEFORE | NULL    |          | root@localhost | 
+---------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+

So, the problem is that any update gets registered in lportal, even if a user logs in, I think this is treated as an update and address_update table gets updated. Is it because modifiedDate changes its value for any activity in the portal?

What we need is to track when only Address information changes or only Phone information changes for a particular user but the problem right now I think is that modifiedDate for these tables changes whenever user say, logs into the portal or does some DB-related operation which is not necessarily updating the Address table. Could somebody recommend a solution to our problem?

Thank you,
Victor.