Pages

Tuesday, October 17, 2017

SQL Server code example: Block UPDATEs to a table using INSTEAD TRIGGER

=========================
General
=========================
Block all updates to a table via INSTEAD OF Triggers.

use my_database
GO
CREATE TRIGGER SitesServiceManager_TG01 ON user.SitesServiceManager
INSTEAD OF UPDATE AS
BEGIN
  RETURN
END

use my_database
GO
CREATE TRIGGER SitesServiceManager_TG02 ON user.SitesServiceManager
INSTEAD OF DELETE AS
BEGIN
  RETURN
END

GO

use my_database
GO
CREATE TRIGGER SitesServiceManager_TG03 ON user.SitesServiceManager
INSTEAD OF INSERT AS
BEGIN
  RETURN
END

GO


Procedures to Enable/Disable triggers:
CREATE PROCEDURE [dbo].[block_updates_on_SitesServiceManager]  AS
BEGIN
  ALTER TABLE user.SitesServiceManager ENABLE TRIGGER SitesServiceManager_TG01 
  ALTER TABLE user.SitesServiceManager ENABLE TRIGGER SitesServiceManager_TG02
  ALTER TABLE user.SitesServiceManager ENABLE TRIGGER SitesServiceManager_TG03 
END

CREATE PROCEDURE [dbo].[allow_updates_on_SitesServiceManager]  AS
BEGIN
  ALTER TABLE user.SitesServiceManager DISABLE TRIGGER SitesServiceManager_TG01 
  ALTER TABLE user.SitesServiceManager DISABLE TRIGGER SitesServiceManager_TG02
  ALTER TABLE user.SitesServiceManager DISABLE TRIGGER SitesServiceManager_TG03
END


Execute procedures to Enable/Disable triggers:
[my_database].[dbo].[allow_updates_on_SitesServiceManager] 
[my_database].[dbo].[block_updates_on_SitesServiceManager] 

No comments:

Post a Comment