--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
Script to Create Trigger for each table to make read-only and block any updates or modification apart from Replication.
Posted by