title: MS SQL Trigger for ETL vs Performance
body: <p>I would need information what might be the <strong>impact for production DB</strong> of creating triggers for ~30 Production tables that capture any Update,Delete and Insert statement and put following information "PK", "Table Name", "Time of modification" to separate table.<br>
  I have limited ability to test it as I have read only permissions to both Prod and Test environment (and I can get one work day for 10 end users to test it).<br>
  I have estimated that number of records inserted from those triggers will be around ~150-200k <strong>daily</strong>.<br>
  Background:
  I have project to deploy Data Warehouse for database that is very customized + there are jobs running every day that manipulate the data. Updated on Date column is not being maintain (customization) + there are hard deletes occurring on tables. We decided to ask DEV team to add triggers like:</p>
  
  <pre><code> CREATE TRIGGER [dbo].[triggerName] ON [dbo].[ProductionTable]
      FOR INSERT, UPDATE, DELETE 
      AS
  
      INSERT INTO For_ETL_Warehouse (Table_Name, Regular_PK, Insert_Date)
      SELECT 'ProductionTable', PK_ID, GETDATE() FROM inserted
  
      INSERT INTO For_ETL_Warehouse (Table_Name, Regular_PK, Insert_Date)
      SELECT 'ProductionTable', PK_ID, GETDATE() FROM deleted
  </code></pre>
  
  <p>on core ~30 production tables.
  Based on this table we will pull delta from last 24 hours and push it to Data Warehouse staging tables.</p>
  
  <p>If anyone had similar issue and can help me estimate how it can impact performance on production database I will really appreciate. (if it works - I am saved, if not I need to propose other solution. Currently mirroring or replication might be hard to get as local DEVs have no idea how to set it up...)
  Other ideas how to handle this situation or perform tests are welcome (My deadline is Friday 26-01).</p>