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

Posted by

--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

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s