Tag: Add

Add trigger to all tables in schema

The code below will add a trigger to all the tables in a specific schema on the Microsoft
SQL-server.

-- Uses the build-in stored procdure in Microsoft SQL-server to run through all tables.
EXEC sp_MSForEachTable 
-- Initial command for the DB to use.
@precommand = 'use <db>',
-- Check if trigger already exists on table. If it does drop/delete it.
@command1 = 
'
IF OBJECT_ID(''[user].[?_trig]'', ''TR'') IS NOT NULL
BEGIN
	DROP TRIGGER [user].[?_trig]
END
',
-- Create trigger on all tables in a specific schema.
@command2 = '
CREATE TRIGGER [?_trig]
ON ?
AFTER INSERT
AS
	SET NOCOUNT ON
	INSERT [<schema>].[<table>] ([<column1>], [table_name], [dtNow])
	SELECT <columnname>, ''?'', CURRENT_TIMESTAMP FROM ?
GO
',
-- Only look at tables in a specific schema.
@whereand = 'and upper(schema_name(schema_id)) = ''<SCHEMA>'''
go