FOR | AFTER | INSTEAD OF

 

The syntax of the trigger is mentioned bellow:

CREATE TRIGGER [ schema_name . ]trigger_name

ON { table | view }

[ WITH <dml_trigger_option> [ ,...n ] ]

{ FOR | AFTER | INSTEAD OF }

{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

[ WITH APPEND ]

[ NOT FOR REPLICATION ]

AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

 

<dml_trigger_option> ::=

    [ ENCRYPTION ]

    [ EXECUTE AS Clause ]

 

<method_specifier> ::=

    assembly_name.class_name.method_name

 

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)

CREATE TRIGGER trigger_name

ON { ALL SERVER | DATABASE }

[ WITH <ddl_trigger_option> [ ,...n ] ]

{ FOR | AFTER } { event_type | event_group } [ ,...n ]

AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

 

<ddl_trigger_option> ::=

    [ ENCRYPTION ]

    [ EXECUTE AS Clause ]

 

<method_specifier> ::=

    assembly_name.class_name.method_name

 

Trigger on a LOGON event (Logon Trigger)

CREATE TRIGGER trigger_name

ON ALL SERVER

[ WITH <logon_trigger_option> [ ,...n ] ]

{ FOR| AFTER } LOGON 

AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

 

<logon_trigger_option> ::=

    [ ENCRYPTION ]

    [ EXECUTE AS Clause ]

 

<method_specifier> ::=

    assembly_name.class_name.method_name

 

In this article we are talking about the parameters

{ FOR | AFTER | INSTEAD OF }

 

FOR|AFTER

When all operations specified in the triggering SQL statement have executed successfully.

 All referential cascade actions and constraint checks also must succeed before this trigger fires. AFTER is the default when FOR is the only keyword specified.

Most of the developer preferred AFTER Instead of FOR.

 

INSTEAD OF

At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, we can define views on views where each view has its own INSTEAD OF trigger.

Suppose VIEW-A Is created from 2 base table named TABLE-1 and TABLE-2.

Now if we want to INSERT records in this VIEW-A, SQL server gives us an error.

Msg 4405, Level 16, State 1, Line 1

View or function 'dbo.View-A' is not updatable because the modification affects multiple base tables.

 

Example:

CREATE TABLE [dbo].[Table-1]

(      [ID]   [INT] NULL,

       [Name] [VARCHAR](20) NULL

)

GO

CREATE TABLE [dbo].[Table-2]

(      [ID]      [INT] NULL,

       [Address] [VARCHAR](200) NULL

)

GO

CREATE View [dbo].[View-A]

AS

SELECT a.ID, a.Name, b.Address

FROM   Table-1 a

       INNER JOIN Table-2 b ON a.ID1 = b.ID

             

INSERT INTO [dbo].[View-A]

            (ID, Name, Address)

VALUES      (1, 'Raja','Kolkata')   

 

  

Msg 4405, Level 16, State 1, Line 1

View or function 'dbo.View-A' is not updatable because the modification affects multiple base tables.

    

So what the options available.

To avoid this error and make a view modifiable we need to create Triggers on the view. These triggers will be used to 'pass' the changes to base tables.

For that we need to create a trigger on the view with INSTEAD OF options to save the record in the underlying base table.

Example of Trigger definition:

CREATE TRIGGER [dbo].[Trig_1]

ON [dbo].[View-A]

INSTEAD OF INSERT

AS

BEGIN

      INSERT INTO Table-1(ID, Name)

      SELECT I.ID, I.Name

      FROM INSERTED I

     

      INSERT INTO Table-2(ID, Address)

      SELECT I.ID, I.Address

      FROM INSERTED I

END

 

Now we can insert the records on the view.

If we also need to perform a UPDATE/DELETE operation, you also need to create additional INSTEAD OF Triggers for UPDATE/DELETE.

 

Note that:

INSTEAD OF not work with the VIEWS that have WITH CHECK OPTION options. If your view has such problem alters your view first.

Hope this article is quite informative and thanking you to provide your valuable time on it.

 

Posted by: MR. JOPYDEEP DAS