Data Import Triggers

[adazzle]

Hiya

We are currently evaluating SQLYog Ultimate v8.22.

We are interested in using the Data Import tools to do a scheduled import (incremental) of our data from a MSSql db.

When we import rows we need to be able to update a row in a table other than the row we've just imported.

So – say we are importing from table 'user'- we then need to write to table 'import_stack' based on the 'User.ID' that was just imported. (this field cannot exist in the original table for a variety of reasons).

When generating the job file the xml is:

Is there a way to run custom sql here – for eg:

Thanks!

[Khushboo]

Hello,

Currently SQLyog is supporting “incremental import” using TRIGGER functionality in Import External Data.

To understand this better, please read this FAQ:

http://webyog.com/faq/content/17/79/en/can-i-perform-incremental-import-with-the-sqlyog-import-external-data-tool.html

To run a custom SQL, you can use “SQL Where” option present in Import External Data (see the screen shots).

Thank You.

Regards,

Khushboo

[adazzle]

I'm aware of that functionality- but it does not cover off the scenario I am asking about. I need to run custom SQL on any table / row as the row is imported – i.e. using the import Trigger. However, as stated before, I can only see how to affect the imported row, not a related row in the source. The SQL Where is only applicable as a filter and so cannot be used here.

Is there a full xml schema definition for the SJA .job files – that should help me see if there is anything useable there.

'Khushboo' wrote on '02:

Hello,

Currently SQLyog is supporting “incremental import” using TRIGGER functionality in Import External Data.

To understand this better, please read this FAQ:

http://webyog.com/faq/content/17/79/en/can-i-perform-incremental-import-with-the-sqlyog-import-external-data-tool.html

To run a custom SQL, you can use “SQL Where” option present in Import External Data (see the screen shots).

Thank You.

Regards,

Khushboo

[Khushboo]

Hello,

Till now what we understood is that you want to add a custom Where clause to update the source row.

Currently this is not possible with SQLyog, but this will be a nice feature to include.

We can add an option in Import External Data –> Advanced Tab –> Trigger Option, where user can define a 'custom where clause' which will affect the source row.

Please correct us, if there is still any misunderstanding from our side.

Thanks & Regards,

Khushboo

[adazzle]

Not quite – what i actually would need is to be able to update another row – NOT the source row. And the update needs to be custom. So the Trigger Option would need to define a CustomSQL Clause to run on each row import.

Say the row imported was ID 100 from Table User. I then need to log this. The trigger would then fire some SQL:

'UPDATE User_Import_Log set last_imported=GetDate() where id = [CURRENT_ROW].ID';

This allows us to keep a log of imports – I understand this could be achieved on the User table itself, but there reasons our architecture needs to use seperate log tables.

'Khushboo' wrote on '02:

Hello,

Till now what we understood is that you want to add a custom Where clause to update the source row.

Currently this is not possible with SQLyog, but this will be a nice feature to include.

We can add an option in Import External Data –> Advanced Tab –> Trigger Option, where user can define a 'custom where clause' which will affect the source row.

Please correct us, if there is still any misunderstanding from our side.

Thanks & Regards,

Khushboo

[Khushboo]

Hi,

I think you got confused, the Trigger Option in Import External Data is for “Updating Source Table” not the “Target Table”.

Quote:

Not quite – what i actually would need is to be able to update another row – NOT the source row

This is not possible through Import External Data, for this you need manually to create a trigger in MySQL server and specify the trigger event for your case.

Thanks & Regards,

Khushboo

[adazzle]

I want to update the source database, but NOT the source row. I.E another row in another table in the database I am importing from.

'Khushboo' wrote on '02:

Hi,

I think you got confused, the Trigger Option in Import External Data is for “Updating Source Table” not the “Target Table”.

This is not possible through Import External Data, for this you need manually to create a trigger in MySQL server and specify the trigger event for your case.

Thanks & Regards,

Khushboo

[Author]

Posts