File Initialize & SQL Triggers

[kstickler]

Is everyone aware that the File Intialize routine in System Manager will result in deleting SQL Triggers from the table?

A week or so ago I did a file initialize on the IMINVLOC table at the request of Exact Support to correct some problems with WMS.

This table had "several" SQL triggers in place. I recently noticed (and so did the users!) that the triggers didn't seem to be running anymore. When I checked the table, they were all missing.

In thinking about "File Initialize" I suppose it makes sense that this happens, as the table is actually "dropped" from the database as part of the process.

Just to verify, I ran the process in our Test Company on a table that I added a trigger to. Sure enough, the triggers were gone after the File Init. I'm assuming a File rebuild will result in the same outcome.

I think all system administrators need to be aware of this and Exact needs to also warn any users when it advises them to run a File Initialize.

 

[jmccall]

What version of Macola was this? I'm using Macola Progression and was under the impression that triggers were not supported in Progression. This may be one of the reasons.

 

[kstickler]

We are on Progression 7.6.300c.

I'm not sure about whether they're "supported".

Our BP recommended them and wrote them for us.

 

[NEmacGuy]

Not only would initialize delete triggers, but rebuilding the table will have the same effect.
Just a note, I hope exact also recommended that you run the WMS update procedure to recreate their "WMS - indexs on your iminvloc_sql file.
I would never take File Initilize lightly, as it does exact what you said, it drops the table and all of the data in it. I believe they would always advise, making a backup of the DB first.
Rebuild - exports the data to a FW text file, drops the table, and then imports the text back into the table.

You can go crazy and setup some other trigger that recreates the trigger on a table when said table is created. I had one client that used triggers on their progression database, and it was ok to rebuild or initialize and the tiggers would auto-recreate.

For what it is worth the above also applies to index's. Some exact products add additional indexs to your database, such as WMS and Business Analytics.

 

[kstickler]

Yes, Exact support also had us run the WMS update procedure. I didn't feel the need to go into detail about the WMS issue on this post, but suffice to say it is resolved.

I did make a backup of the table prior to the File Init, but a DB backup would have captured the triggers.

I didn't catch that they were missing until we were through an entire backup set. Luckily I have copies, in email or in our IT work order system (eSynergy!), so I can reapply them...well, all except for one that I will have to rewrite.

 

[dgillz]

Just curious what you are accomplishing with the triggers? Is there any reason tis cannot be handled with Event Manager or Flexibility?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[kstickler]

We are using the triggers for the following:

Auto populating the bulk weight file (WMS) when an item master record is added or changed.

On item master update, syncing the buyer code across all warehouse locations (Inventory Item Location) based on the buyer code in item master.

Inventory Item Location, setting lead time and dock to stock to company defaults.

Syncing standard cost across warehouse locations.

I could go on...

 

[dgillz]

You could do all that with Event Manager without the need to worry about losing your triggers.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[tpllc]

Triggers act when ever the data is updated. Event manager would have to execute on a schedule. The trigger just waits until the data is updated and immediately performs the defined action. There is a purpose and a need for both. But triggers can be a pain.

Steve Henley
Trianglepartners.com
Exact Software consulting, sales and implementations.

If the only tool you can use is a hammer then all your problems look like nails.

 

[abaldwin]

Triggers are not a pain. Actually that would be a nice way to accomplish something not done in flex. Macola initialize table is ignorant in that it drops the table. (Since tringgers are part of the table they "disapear") Then macola runs a script to build the table from scratch.

A much better initialize routine would be to export the records. Delete the records, and import them. I mean after-all. How does the design of a table get screwed up by an application.

The other time you have problems with triggers is when an upgrade to Macola changes tables. Macola typically exports, drops, rebuilds, and imports the tables rather than just adding a field, index, or what not.

Always been a pet peeve of mine with Macola on SQL.



Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.