Triggers on Views

Trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.

E.g.1

CREATE TRIGGER <TriggerName> ON <View_Name or Table Name>

INSTEAD OF INSERT, UPDATE, DELETE

AS

BEGIN

PRINT ‘TRIGGER CALLED’

END

E.g.2 – DDL Trigger

USE AdventureWorks

GO

CREATE TRIGGER PreventDropSP

ON DATABASE

FOR DROP_PROCEDURE

AS

PRINT ‘Dropping Procedure is not allowed.

ROLLBACK;

GO

Triggers Dos and Don’ts

1. Types are: – DML, DDL, or logon trigger in SQL Server 2012.
2. Trigger can be created only in the current database, but it can refer objects outside the current database.
3. DML trigger cannot be defined on local or global temporary tables
4. A view can be referenced only by an INSTEAD OF trigger means we can have only Instead of Trigger on View.
5. INSTEAD OF cannot be specified for DDL or logon triggers.
6. At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, you can define views on views where each view has its own INSTEAD OF trigger.
7. INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION. SQL Server raises an error when an INSTEAD OF trigger is added to an updatable view WITH CHECK OPTION specified. The user must remove that option by using ALTER VIEW before defining the INSTEAD OF trigger.
8. After triggers cannot be defined on views.
9. For Instead if triggers, the Delete option is not allowed on tables that have a referential relationship specifying a cascade action on DELETE.
10. Instead of Delete/Update triggers cannot be defined on a table that has a foreign key with a cascade on Delete/Update action defined.
11. Truncate table statement does not activate a trigger, because the operation does not log individual rows.
    1. Triggers can be nested to a maximum of 32 levels to disable nexted triggers set the nested triggers option to 0 (off).

Recursive triggers is also off if nested trigger is off