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 executed instead of the 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

CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test VALUES ('Jay',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('Linda',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);

Audit table for checking traces
CREATE TABLE 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:
CREATE TRIGGER Inserted on Employee_Test for insert
AS
DECLARE @Emp_ID INT;
DECLARE @Emp_name Varchar(100);
DECLARE @Emp_Sal Decimal (10,2);
SELECT @Emp_ID=I.Emp_ID FROM Inserted I;
SELECT @Emp_name=I.Emp_name FROM Inserted I;
SELECT @Emp_Sal=I.Emp_Sal FROM Inserted I;
INSERT INTO Employee_Test_Audit VALUES (@Emp_ID,@Emp_name,@Emp_Sal,'Inserted',getdate());
PRINT 'AFTER INSERT TRIGGER FIRED'

The CREATE TRIGGER statement is used to create the trigger. THE ON clause specifies the table name on which the trigger is to be attached. The FOR INSERT specifies that this is an AFTER INSERT trigger. In place of  FOR INSERT, AFTER INSERT can be used. Both of them mean the same.

Insert one row into the table

INSERT INTO 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:-

CREATE TRIGGER updated on Employee_Test for update
AS
DECLARE @Emp_ID INT;
DECLARE @Emp_name Varchar(100);
DECLARE @Emp_Sal Decimal (10,2);
SELECT @Emp_ID=I.Emp_ID FROM Inserted I;
SELECT @Emp_name=I.Emp_name FROM Inserted I;
SELECT @Emp_Sal=I.Emp_Sal FROM Inserted I;
INSERT INTO Employee_Test_Audit VALUES (@Emp_ID,@Emp_name,@Emp_Sal,'updated',getdate());
PRINT 'AFTER INSERT TRIGGER FIRED'

update Employee_Test set emp_name='pppp' where emp_id=1

now check the data in audit

Emp_ID                Emp_name         Emp_Sal               Audit_Action     Audit_Timestamp
1              pppp     1000.00 updated               2016-11-16 14:00:13.243

After Delete Trigger:-

This trigger is fired after a delete on the table. Let’s create the trigger as:

CREATE TRIGGER DELETED1 on Employee_Test for DELETE
AS
DECLARE @Emp_ID INT;
DECLARE @Emp_name Varchar(100);
DECLARE @Emp_Sal Decimal (10,2);
SELECT @Emp_ID=I.Emp_ID FROM DELETED1 I;
SELECT @Emp_name=I.Emp_name FROM DELETED1 I;
SELECT @Emp_Sal=I.Emp_Sal FROM Inserted I;
INSERT INTO Employee_Test_Audit VALUES (@Emp_ID,@Emp_name,@Emp_Sal,'DELETED',getdate());
PRINT 'AFTER INSERT TRIGGER FIRED'

CREATE TRIGGER afterDelete ON [dbo].[Employee_Test]
AFTER DELETE
AS
      declare @empid int;
      declare @empname varchar(100);
      declare @empsal decimal(10,2);
      declare @audit_action varchar(100);

      select @empid=d.Emp_ID from deleted d;   
      select @empname=d.Emp_Name from deleted d;     
      select @empsal=d.Emp_Sal from deleted d; 
      set @audit_action='Deleted -- After Delete Trigger.';

      insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
      values(@empid,@empname,@empsal,@audit_action,getdate());

      PRINT 'AFTER DELETE TRIGGER fired.'
GO

To disable/enable triggers:-

ALTER TABLE Employee_Test {ENABLE|DISBALE} TRIGGER ALL

For Specific Trigger disable

ALTER TABLE Employee_Test DISABLE TRIGGER trgAfterDelete

INSTEAD OF:-

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

INSTEAD OF TRIGGERS can be classified further into three types as:

  • 1.    INSTEAD OF INSERT Trigger.
  • 2.    INSTEAD OF UPDATE Trigger.
  • 3.    INSTEAD OF DELETE Trigger.

Let’s create an Instead Of Delete Trigger as:


CREATE TRIGGER InsteadOfDelete ON [dbo].[Employee_Test]
INSTEAD OF DELETE
AS
      declare @emp_id int;
      declare @emp_name varchar(100);
      declare @emp_sal int;
     
      select @emp_id=d.Emp_ID from deleted d;
      select @emp_name=d.Emp_Name from deleted d;
      select @emp_sal=d.Emp_Sal from deleted d;

      BEGIN
            if(@emp_sal>1200)
            begin
                  RAISERROR('Cannot delete where salary > 1200',16,1);
                  ROLLBACK;
            end
            else
            begin
                  delete from Employee_Test where Emp_ID=@emp_id;
                  COMMIT;
                  insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
                  values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());
                  PRINT 'Record Deleted -- Instead Of Delete Trigger.'
            end
      END
GO

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:

delete from Employee_Test where Emp_ID=4

This will print an error message as defined in the RAISE ERROR statement 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.