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