asked
Is there a way to generate an insert from another insert statement without using functions?
Hi experts. I have a translation table that is related to almost every Table in the Database. I need to populate this table everytime I make an insert in one of this Tables. In ORACLE I did this way: INSERT INTO M_ANYTABLE ( ID_ANYTABLE , TEXT , DATE , TRANS_ID ) VALUES ( 'PEPE' , 'PEPE' , SYSDATE , FN_TRD_NOCOMMIT('TEXT TRANSLATE') ); Function FN_TRD_NOCOMMIT make the inserts in translations table. I can't replicate this in SQLServer because functions cannot change the Database in any way. I fixed this making Triggers (one for each Table). Although I know is not a elegant solution. What I am asking is if anyone can come with a better way of making inserts through the original insert statement (above) or any other solution that you have deployed for making inserts through a function or whatever doesn't imply using one trigger for each Table. Any ideas? I hope I made my point correctly. Sorry in case of bad English. Thanks in advance.
It sounds like you could achieve what you want by using a transaction to perform the insert and then execute a procedure to do whatever is necessary to insert into your translation table. In your example, you are only inserting 1 row at a time, so calling the procedure each time shouldn't be an issue. If you are inserting batches of rows, the trigger method will almost definitely give you better performance because it too will operate on the entire batch rather than just one row. Alternatively, you could change the parameters of the procedure to include a range of IDs or even a table-valued parameter.
15 People are following this question.
