Is there a way in SQLServer to generate an insert from another insert statement without using functions?

Question

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.

Answer 1

I am not sure if I understood you right, but you can use OUTPUT for thet:

CREATE TABLE #test
(
    ID_ANYTABLE VARCHAR(123),
           TEXT VARCHAR(123) ,
           DATE DATETIME
);

CREATE TABLE #test2
(
    ID_ANYTABLE VARCHAR(123),
           TEXT VARCHAR(123) ,
           DATE DATETIME
)

 INSERT  INTO #test
         ( ID_ANYTABLE ,
           TEXT ,
           DATE   
         )  
 OUTPUT INSERTED.ID_ANYTABLE, INSERTED.TEXT, INSERTED.DATE INTO    #test2      
 VALUES  ( 'PEPE' ,
           'PEPE' ,
           GETDATE() 
         );

SELECT * FROM #test
SELECT * FROM #test2