Pages

Wednesday, November 16, 2016

Designing and implementing triggers in Microsoft SQL Server

A trigger is a special kind of stored
procedure that automatically executes when an event occurs in the database
server. DML triggers execute when a user tries to modify data through a data
manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE
statements on a table or view. These triggers fire when any valid event is
fired, regardless of whether or not any table rows are affected.

EXECUTE AS

specifies the security context under which the trigger is executed. Enables you
to control which user account the instance of SQL Server uses to validate
permissions on any database objects that are referenced by the trigger. This
option is required for triggers on memory-optimised tables.

NATIVE_COMPILATION
indicates that the trigger is natively compiled. This option is required for
triggers on memory-optimized tables.

SCHEMABINDING
ensures that tables that are referenced by a trigger cannot be dropped or
altered.

This option is required for triggers on memory-optimized tables
and is not supported for triggers on traditional tables.

FOR | AFTER
AFTER specifies that the DML trigger is fired only 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. AFTER
triggers cannot be defined on views.

INSTEAD OF
Specifies that the DML trigger is executedinstead ofthe triggering SQL statement,
therefore, overriding the actions of the triggering statements.

INSTEAD
OF cannot be specified for DDL or logon triggers.

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.

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.

{ [ DELETE ] [ , ] [ INSERT
] [ , ] [ UPDATE ] }
Specifies the data modification statements that activate the DML trigger when
it is tried against this table or view. At least one option must be specified.
Any combination of these options in any order is allowed in the trigger
definition.

For INSTEAD OF triggers, the DELETE option is not allowed on
tables that have a referential relationship specifying a cascade action ON
DELETE. Similarly, the UPDATE option is not allowed on tables that have a
referential relationship specifying a cascade action ON UPDATE.

Triggers
are classified into two main types:

1.After Triggers (For Triggers)

2.Instead Of Triggers

After Triggers (For Triggers)

These
triggers run after an insert, update or delete on a table. They are not supported for views.AFTER TRIGGERS can be classified further into
three types as:

1.AFTER INSERT Trigger.

2.AFTER UPDATE Trigger.

3.AFTER DELETE Trigger.

Main Table

CREATETABLE Employee_Test

(

Emp_ID INTIdentity,

Emp_name Varchar(100),

Emp_Sal Decimal (10,2)

)

INSERTINTO Employee_Test VALUES ('Jay',1000);

INSERTINTO Employee_Test VALUES ('Rick',1200);

INSERTINTO Employee_Test VALUES ('Linda',1100);

INSERTINTO Employee_Test VALUES ('Stephen',1300);

INSERTINTO
Employee_Test VALUES ('Maria',1400);

Audit table for checking traces

CREATETABLE Employee_Test_Audit

(

Emp_ID int,

Emp_name varchar(100),

Emp_Sal decimal (10,2),

Audit_Action varchar(100),

Audit_Timestamp datetime

)

After Insert
Trigger:-

This trigger is fired after
an INSERT on the table. Let’s create the trigger as:

TheCREATE TRIGGERstatement
is used to create the trigger.THE ONclause
specifies the table name on which the trigger is to be attached. TheFOR INSERTspecifies
that this is anAFTER INSERTtrigger. In
place of FOR INSERT,AFTER INSERTcan be
used. Both of them mean the same.

Insert one row into the
table

INSERTINTO Employee_Test VALUES ('Pooja',1400);

Now check the data in table

Emp_ID Emp_name Emp_Sal Audit_Action Audit_Timestamp

13 Pooja 1400.00 Inserted 2016-11-16 12:30:35.773

After Update
Trigger:-

This trigger is fired after
an update on the table. Let’s create the trigger as:-

Specifies that the DML trigger is executedinstead of the triggering SQL statement,
therefore, overriding the actions of the triggering statements. INSTEAD OF
cannot be specified for DDL or logon triggers.

This trigger will prevent
the deletion of records from the table where Emp_Sal > 1200. If such a
record is deleted, the Instead Of Trigger will rollback the transaction,
otherwise the transaction will be committed. Now, let’s try to delete a record
with the Emp_Sal >1200 as:

deletefrom
Employee_Test where Emp_ID=4

This will print an error
message as defined in theRAISE ERRORstatement as:

Msg 50000, Level 16,
State 1, Procedure InsteadOfDelete, Line 15Cannot delete where
salary > 1200Msg 3609, Level 16,
State 1, Line 1The transaction ended in the trigger. The
batch has been aborted.