Instead of a trigger to insert on all tables

Question

Can someone please indicate the source of the generic spelling, not the insert trigger for all tables in the database. I would like to run a stored procedure that will create insert triggers for all tables in db instead of triggers.

Answer 1

Not understanding why you want instead of a trigger on each individual table, and what else do you plan to do in the resulting code, except for the inserted values ​​in the base table (exactly the same as it would be if there weren't a trigger at all), that's what i came up with. You will notice that it disables the trigger if it already exists, so you can run it several times in the same database without "existing" errors. It ignores IDENTITY, ROWGUIDCOL, computed columns, and TIMESTAMP / ROWVERSION columns. Finally, in the end I will show how you can quickly check, and not execute (which is commented out) the output of the script (up to 8K) and pass it in XML if you want to see more (up to 64K). There is no guarantee that you can return all this to SSMS depending on the number of tables / columns. If you want to check it and / or run it manually, you can create a table in which the value is stored - then you can pull it out using the application or whatever you have. Now, if you want this to be done automatically, you can follow the Yuck point - save it as a stored procedure and create a DDL trigger that responds to certain 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 AS ( SELECT [object_id], s = OBJECT_SCHEMA_NAME([object_id]), n = OBJECT_NAME([object_id]) FROM sys.tables WHERE is_ms_shipped = 0 ) SELECT @s += 'IF OBJECT_ID(''dbo.ioTrigger_' + ts + '_' + tn + ''') IS NOT NULL DROP TRIGGER [dbo].[ioTrigger_' + ts + '_' + tn + ']; G' + 'O CREATE TRIGGER ioTrigger_' + ts + '_' + tn + ' ON ' + QUOTENAME(ts) + '.' + QUOTENAME(tn) + ' INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; -- surely you must want to put some other code here? INSERT ' + QUOTENAME(ts) + '.' + QUOTENAME(tn) + ' ( ' + ( SELECT @t + @t + name + ',' + @cr FROM sys.columns AS c WHERE c.[object_id] = t.[object_id] AND is_identity = 0 AND is_rowguidcol = 0 AND is_computed = 0 AND system_type_id <> 189 FOR XML PATH(''), TYPE ).value('.[1]', 'NVARCHAR(MAX)') + '--' + @cr + @t + ')' + @cr + @t + 'SELECT ' + ( SELECT @t + @t + name + ',' + @cr FROM sys.columns AS c WHERE c.[object_id] = t.[object_id] AND is_identity = 0 AND is_rowguidcol = 0 AND is_computed = 0 AND system_type_id <> 189 FOR XML PATH(''), TYPE ).value('.[1]', 'NVARCHAR(MAX)') + '--' + @cr + @t + 'FROM inserted; END' + @cr + 'G' + 'O' + @cr FROM t ORDER BY ts, tn; SELECT @s = REPLACE(@s, ',' + @cr + '--' + @cr, @cr); -- you can inspect at least part of the script by running the -- following in text mode: SELECT @s; -- if you want to see more of the whole thing (but not necessarily -- the whole thing), run this in grid mode and click on result: SELECT CONVERT(XML, @s); 

A few caveats:

1) I do not deal with sparse columns, collections of xml, filestream, etc., so if you have attractive tables, you may encounter complications with some of these functions.

2) the trigger name is not really “protected” - you may have a schema named foo, another schema named foo_bar, and then a table in foo called bar_none, and the table in foo_bar is called none. This will duplicate the trigger name because I use the underscore as a delimiter. I complained about it with the help of CDC, but they closed the error because they did not fix it . You just need to keep in mind if you use underscore schemes.

Answer 2

You can create database triggers for DDL statements in SQL Server 2008+.

If you need DML ( INSTEAD OF INSERT ) triggers in each table in the database, you will have to either manage them yourself or try to create a DDL trigger at the database level that is responsible for creating or updating INSTEAD OF INSERT triggers for any CREATE or ALTER table statements . It can be hairy and will almost certainly require the use of dynamic SQL.

Out of curiosity, are you trying to create some kind of audit mechanism?