sql - Instead of Trigger for Insert on all tables -

can please provide source on how write generic instead of trigger insert tables in database. want run stored procedure create instead of insert triggers tables in db .

without understanding why want instead of trigger on every single table, , else plan in resulting code aside insert supplied values base table (just have happened if there no instead of trigger @ all), here came with. you'll note drops trigger if exists, can run multiple times in same database without "already exists" errors. ignores identity, rowguidcol, computed columns, , timestamp/rowversion columns. @ end show how can inspect, instead of execute (which commented out) output script (up 8k), , cast xml if want see more (up 64k). no guarantees can return whole thing in ssms depending on how many tables/columns there are. if want check and/or run manually may want create table stores value - can pull out app or have you. if want execute automatically can follow yuck's point - save stored procedure , create ddl trigger responds ddl events (create table etc).

set nocount on;  declare      @cr varchar(2) = char(13) + char(10),     @t  varchar(1) = char(9),     @s  nvarchar(max) = n'';  ;with t (     select [object_id],       s = object_schema_name([object_id]),      n = object_name([object_id])     sys.tables is_ms_shipped = 0      ) select @s += 'if object_id(''dbo.iotrigger_' + t.s + '_' + t.n + ''') not null     drop trigger [dbo].[iotrigger_' + t.s + '_' + t.n + ']; g' + 'o create trigger iotrigger_' + t.s + '_' + t.n + '     on ' + quotename(t.s) + '.' + quotename(t.n) + '     instead of insert  begin     set nocount on;      -- surely must want put other code here?      insert ' + quotename(t.s) + '.' + quotename(t.n) + '     ( ' +  (     select @t + @t + name + ',' + @cr         sys.columns c         c.[object_id] = t.[object_id]         , is_identity = 0         , is_rowguidcol = 0         , is_computed = 0     , system_type_id <> 189     xml path(''), type ).value('.[1]', 'nvarchar(max)') + '--'  + @cr + @t + ')' + @cr + @t + 'select  ' +  (     select @t + @t + name + ',' + @cr         sys.columns c         c.[object_id] = t.[object_id]         , is_identity = 0         , is_rowguidcol = 0         , is_computed = 0     , system_type_id <> 189     xml path(''), type ).value('.[1]', 'nvarchar(max)') + '--' + @cr + @t + 'from         inserted; end' + @cr + 'g' + 'o' + @cr t order t.s, t.n;  select @s = replace(@s, ',' + @cr + '--' + @cr, @cr);  -- can inspect @ least part of script running -- following in text mode:  select @s;  -- if want see more of whole thing (but not -- whole thing), run in grid mode , click on result:  select convert(xml, @s); 

a couple of caveats:

1) don't deal sparse columns, xml collections, filestream etc. if have fancy tables might run complications of features.

2) name of trigger isn't "protected" - have schema called foo, schema called foo_bar, , table in foo called bar_none , table in foo_bar called none. lead duplicate trigger name because use underscore separator. complained cdc they closed bug won't fix. keep in mind if happen use schemas underscores.