Script to Create Trigger for each table to make read-only and block any updates or modification apart from Replication.

--Create Triggers for protection

 

SET NOCOUNT ON

;With AllTables as (SELECT NAME, SCHEMA_NAME(t.[schema_id]) AS SchemaName FROM sys.tables AS t WHERE t.is_ms_shipped = 0 )

SELECT

'CREATE TRIGGER tr_' + SchemaName + '_' + NAME + ' ON [' + SchemaName + '].[' +NAME + ']

INSTEAD OF INSERT, DELETE, UPDATE

NOT FOR REPLICATION

AS

RAISERROR(''Database is read-only! Contact your DBA.'', 16, 1)

GO

'

FROM AllTables AS t

ORDER BY t.name

 

 

 

-- ROllback Script for Dropping Triggers

SET NOCOUNT ON

PRINT '-- ROllback Trigger Script'

 

;With AllTables as (SELECT NAME, SCHEMA_NAME(t.[schema_id]) AS SchemaName FROM sys.tables AS t WHERE t.is_ms_shipped = 0 )

SELECT

'DROP TRIGGER '+SchemaName+'.tr_' + SchemaName + '_' + NAME + '

GO

'

FROM AllTables AS t

ORDER BY t.name