Create trigger

-- =============================================
-- Create trigger basic template(After trigger)
-- =============================================
IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE  name = N'<trigger_name, sysname, trig_test>'
       AND       type = 'TR')
    DROP TRIGGER <trigger_name, sysname, trig_test>
GO

CREATE TRIGGER <trigger_name, sysname, trig_test>
ON <table_name, sysname, pubs.dbo.sales>
FOR DELETE, INSERT, UPDATE
AS
BEGIN
    RAISERROR (50009, 16, 10)
END
GO

 

-- =============================================
-- Create trigger contained If UPDATE(column)
-- =============================================
IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE  name = N'<trigger_name, sysname, trig_test>'
       AND       type = 'TR')
    DROP TRIGGER <trigger_name, sysname, trig_test>
GO

CREATE TRIGGER <trigger_name, sysname, trig_test>
ON <table_or_view_name, sysname, pubs.dbo.sales>
FOR INSERT, UPDATE
AS
If UPDATE(<column_1, sysname, stor_id>) OR UPDATE(<column_2, sysname, ord_num>)
BEGIN
    RAISERROR ('These columns should never be updated', 16, 1)
    ROLLBACK TRANSACTION
END
GO

-- =============================================
-- Create trigger contained If (COLUMNS_UPDATED())
-- =============================================
IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE  name = N'<trigger_name, sysname, trig_test>'
       AND       type = 'TR')
    DROP TRIGGER <trigger_name, sysname, trig_test>
GO

CREATE TRIGGER <trigger_name, sysname, trig_test>
ON <table_or_view_name, sysname, pubs.dbo.sales>
FOR INSERT, UPDATE
AS
--eg. check if all of column 2, 3, 4 are updated
IF (COLUMNS_UPDATED() & 14) = 14
BEGIN
    RAISERROR ('These columns can not be updated at the same time', 16, 1)
    ROLLBACK TRANSACTION
END
GO

-- =============================================
-- Create basic Instead Of Trigger
-- =============================================
IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE  name = N'<trigger_name, sysname, trig_test>'
       AND       type = 'TR')
    DROP TRIGGER <trigger_name, sysname, trig_test>
GO

CREATE TRIGGER <trigger_name, sysname, trig_test>
ON <table_or_view_name, sysname, pubs.dbo.sales>
INSTEAD OF INSERT
AS
BEGIN
    RAISERROR (50009, 16, 10)
    EXEC sp_who
END
GO