Passing of old and new trigger details to a generic function or procedure
Hi all.
I have a requirement to pass all the new and old column information from a database trigger ( for the purposes of this discussion, it will be a before row, insert, update and delete trigger) to a procedure/function ( either is fine).
My first thought was to create a separate routine for each table I wish to implement this in, which has a new and old input parameter for every column, however I realised I needed a solution where the called routine needs to be generic (because we'll let other developers call the same routine and we dont want them to have to create their own version for their table).
This presents a problem as every table I wish to implement this on could have a completely different amount of columns (and dataypes). The called routine will operate under a pragma autnonmous_transaction (as it will be performing commits) so I need to pass all the fields across in one go.
Another idea I had was to create an type object with (potentially) 300 generic fields that I assign the values to and pass that as a parameter, however I could never know before hand what datatypes are needed, CLOBS, DATE, VARCHAR, etc...
Does anyone have any ideas on how I can accomplish this with a generic routine?
Regards,
Greg.