SQL Trigger Alternatives

Well the problem is that I can't make changes to the database tables themselves b/c it is a proprietary application that access the tables (our ERP).

Hi,

Many companies use custom triggers in Dynamics GP, and you can use them too without too much worry.  Technically you are correct that your custom triggers will not supported by Microsoft, just as many specific customizations are not supported, but that doesn't need to prevent you from modifying your Dynamics GP system to reduce costs or increase efficiency in your business.

Depending on what exactly you need to do using triggers, the triggers may be relatively simple, or they may be very complex.  The complexity will determine the level of help or expertise that you may need before you deploy a trigger in your production Dynamics GP environment.

I've written a blog post about a recent experience I had developing a trigger for a GP client, and how I was able to debug the trigger to get it working properly.

http://dynamicsgpland.blogspot.com/2009/10/using-and-debugging-sql-triggers-with.html

If you have any specific questions about what you are trying to do, let me know and I'll try and give you some guidance.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional

I am really just wanting to take a snapshot of the SOP10100 table when an order hits a certain batch.  For example trigger an insert into [another db].[dbo].[another db table] when bachnum='backorders'.  Will something like this work?

Hi,

Yes, that would probably work, but based on your description (sounds relatively simple), I think you might have the option of either a trigger or a query, as you suggested in your original post.  But it depends on your ultimate requirements for the copied data, i.e. do you need the records to be current up to the second?  Or is 2 to 5 minute latency acceptable?

For a query, what you can do is make sure to include the DEX_ROW_ID field in your 'replica' table.  The query can then do a query of the records in SOP10100, left outer join REPLICA where BACHNUMB = 'Backorders' AND REPLICA.DEX_ROW_ID IS NULL.  That would give you records that are in SOP10100, but not in REPLICA (i.e. new records).  You then take that result set, and use an INSERT INTO statement to copy the records into your REPLICA table.  You can then use SQL Agent to schedule this statement to run every 5 minutes, or maybe even every 2 mins, since it should run in less than 1 second.

You could also use a trigger, but the general caveats with the trigger are if your SQL statement has a delay or failure, it could cause GP to hang, or prevent a record from being properly saved or updated.  It basically has the potential of interfering with GP's normal operation.  With debugging and safe coding, you can minimize risk, but it typically takes a little more time.  And then you do have to remember to keep track of trigger, make sure it works after an upgrade, etc.

One problem I can think of for either approach is that it is theoretically possible for changes to be made to records in SOP10100.  So if changes were made, do you want to copy the updated record, update the existing record, or not do anything?  Same goes for deleted records--if someone deletes a SOP record, do you care?

If you need to track inserts, updates, and deletes, then a trigger is probably easier.  If the records aren't being updated or deleted, then a query might be easier.

Let me know what you think.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional

Well that the last problem you propose is where I am currently stuck.  Right now I am running a query against the database each night.  I then insert into the other database table with an appended EXECUTED_DATE field that tells me when it was executed.  This is fine because my DB isn't going to grow wildly out of control doing this once a night.  If the record is gone, deleted, posted, etc. then it will not be transferred over to the other database during the insert into and therefore I can see how long the order was in our system and each night what batch it was in.

This is where I run into my problem.  I don't think the information that I am looking for is going to do me any good only looking at it nightly.  I think that 1-2 minute updates would be fine for a query, but I DO want to track when the item is updated (i.e. moved from the orders batch to the backorders batch).  I will then have fairly real time tracking on how long an order stayed in each batch in the system.  This is where the trigger comes in b/c I can only execute when an update, insert, delete is performed.  I WOULD prefer to do this with a scheduled query and not a trigger if possible, but I don't see how it could be done in the way I want it.

Any more thoughts?

can you use sql server builtin auditting features?


If you just want to track insert/update/delete on a table,  would a database audit specification work?

http://technet.microsoft.com/en-us/library/cc280404.aspx

I have looked at auditing as an option, but I don't feel like it is customizable enough to do what I am looking to do.  Tell me if you think I am mistaken!

the auditting itself may not be customizable,

but once you have your raw audit data you could write your own procedure to move it into other tables with different structure and modify it as you need and then build reports or other apps on top of your own tables.

simplest form is...
insert into your_audit_table  SELECT * FROM sys.fn_get_audit_file(......)

but you could alter the data as you select it if you want too