KG Muthukumar Wrote:
-------------------------------------------------------
> Thanks Irek.. much appreciate your help and will
> keep your avice in mind the next time..
>
> a supplemental question to the above:
>
> While the SQL you provided works for a one-time
> load, I am also planning for an incremental load
> into the Target table when ever the source table
> record is updated or a new row is added to the
> source table - I would like to do this as a
> trigger on insert/update..
>
> any thoughts on how the trigger SQL might look..
>
Cannot be done using triggers.
According to docs:
http://dev.mysql.com/doc/refman/5.5/en/faqs-triggers.html#qandaitem-B-5-1-9
Quote
B.5.9: Can triggers access tables?
A trigger can access both old and new data in its own table. A trigger can also affect other tables,
but it is not permitted to modify a table that is already being used (for reading or writing)
by the statement that invoked the function or trigger.
When a new record is inserted, a value of STATUS_CREATE_DATETIME from the next record cannot be calculated. So the only way is to update
previous record basing on values from the current (just being inserted) record.
Since the trigger cannot modify its own table, it can't be done using the trigger.