Tuesday 8 August 2017

Trigger in 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 INSERTING, UPDATE, or DELETE statements on a table or view.

Type of Trigger

Ø  DML trigger
Ø  DDL trigger
Ø  Log on trigger
Ø  CLR Triggers

DML Trigger

There are two type of DML trigger. It is faired automatically in response to DML (Insert, Update and delete) event.
Ø   After Trigger (For trigger)
Ø  Instead of Triggers

After Triggers

These triggers are executed after an action such as Insert, Update or Delete is performed. For example  If we insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.
Let see the example
I have a table
CREATE TABLE [EMP](
                [ID] [int] IDENTITY(1,1) NOT NULL,
                [Name] [varchar](50) NULL,
                [empAdd] [varchar](50) NULL,
                [Mobile] [varchar](10) NULL
           )
Inserting some records some


Now I am creating an audit table which stores the information of insert, update and delete.
create table tblAuditEmp
  (
   ID INT IDENTITY (1,1),
   Audit_Desc varchar(1000)
   )

This table has all audit info.
Now I am going to create an insert trigger on EMP tale.
create  trigger trg_Emp_insert on EMP
for insert
as
begin
select * from inserted
end

Trigger has been created. Now I am inserting


 Inserted table
INSERTED logical table will hold the rows which are inserted by the INSERT and UPDATE statement.
Now I am writing the script to insert record in audit table.
http://bageshkumarbagi-msbi.blogspot.in/2017/08/inserted-and-deleted-logical-tables-in.html


Create trigger trg_Emp_insert on EMP
for insert
AS
declare @emp_id int, @emp_name varchar(55), @emp_empadd varchar(100),
        @emp_Mob varchar(100), @audit_action varchar(100),@str VARCHAR(1000);
select @emp_id=i.ID from inserted i;
select @emp_name=i.Name from inserted i;
select @emp_empadd =i.empAdd from inserted i;
select @emp_Mob = i.Mobile from inserted i;
SET @audit_action='Inserted Record ,trg_Emp_insert.';
BEGIN

 SET @str = 'Employee ID '+ cast(@emp_id as varchar) + ' is inserted on '+ cast(getdate() as varchar) +' Employee Name : ' + @emp_name
                                                + 'Employee Add : ' + @emp_empadd + ' Mobile : '+ @emp_Mob + '   ' + @audit_action;

Insert into tblAuditEmp (Audit_Desc) values (@str);
END


There is no record in audit table


Now I am inserting a record in emp table.
Record inserted


Now I am seeing the Audit table.



Trigger for Update
Now I am creating a new trigger for update.

Create trigger trg_Emp_update on EMP
for Update
AS
declare @emp_id int, @emp_name varchar(55), @emp_empadd varchar(100),
        @emp_Mob varchar(100), @audit_action varchar(100),@str VARCHAR(1000);
select @emp_id=i.ID from inserted i;
select @emp_name=i.Name from inserted i;
select @emp_empadd =i.empAdd from inserted i;
select @emp_Mob = i.Mobile from inserted i;
SET @audit_action='Updated Record ,trg_Emp_update.';
BEGIN

 SET @str = 'Employee ID '+ CAST( @emp_id as VARCHAR) + ' is updated on '+ cast(getdate() as varchar) +' Employee Name : ' + @emp_name
                                                + 'Employee Add : ' + @emp_empadd + ' Mobile : '+ @emp_Mob + '   ' + @audit_action;

Insert into tblAuditEmp (Audit_Desc) values (@str);
END

Updating the record.


Now see the records in audit table.


Trigger for Delete
When we deleting the record it will execute and insert record in audit table.
Create trigger trg_Emp_Delete on EMP
for delete
AS
declare @emp_id int, @emp_name varchar(55), @emp_empadd varchar(100),
        @emp_Mob varchar(100), @audit_action varchar(100),@str VARCHAR(1000);
select @emp_id=i.ID from deleted i;
select @emp_name=i.Name from deleted i;
select @emp_empadd =i.empAdd from deleted i;
select @emp_Mob = i.Mobile from deleted i;
SET @audit_action='deleted Record ,trg_Emp_update.';
BEGIN

 SET @str = 'Employee ID '+ CAST( @emp_id as VARCHAR) + ' is deleted on '+ cast(getdate() as varchar) +' Employee Name : ' + @emp_name
                                                + 'Employee Add : ' + @emp_empadd + ' Mobile : '+ @emp_Mob + '   ' + @audit_action;

Insert into tblAuditEmp (Audit_Desc) values (@str);
END

Now I am deleting the record from emp table.


 Deleted table
DELETED logical table will hold the rows which are deleted from the trigger table (i.e. the table on which the trigger is defined) by the DELETE.

Read: INSERTED and DELETED Logical Tables in Sql Server


Instead of Triggers

These triggers are executed instead of any of the Insert, Update or Delete operations. For example, let’s say we write an Instead of Trigger for Delete operation, and then whenever a Delete is performed the Trigger will be executed first and if the Trigger deletes record then only the record will be deleted.
Delete Trigger
Create trigger trg_Emp_Delete_Instead_of on EMP
Instead of delete
AS
declare @emp_id int, @emp_name varchar(55), @emp_empadd varchar(100),
        @emp_Mob varchar(100), @audit_action varchar(100),@str VARCHAR(1000);
select @emp_id=i.ID from deleted i;
select @emp_name=i.Name from deleted i;
select @emp_empadd =i.empAdd from deleted i;
select @emp_Mob = i.Mobile from deleted i;
SET @audit_action='deleted Record ,trg_Emp_update.';
BEGIN

 SET @str = 'Employee ID '+ CAST( @emp_id as VARCHAR) + ' is deleted on '+ cast(getdate() as varchar) +' Employee Name : ' + @emp_name
                                                + 'Employee Add : ' + @emp_empadd + ' Mobile : '+ @emp_Mob + '   ' + @audit_action;

Insert into tblAuditEmp (Audit_Desc) values (@str);
END


Trigger update
Create trigger trg_Emp_update_Instead_of on EMP
Instead of Update
AS
declare @emp_id int, @emp_name varchar(55), @emp_empadd varchar(100),
        @emp_Mob varchar(100), @audit_action varchar(100),@str VARCHAR(1000);
select @emp_id=i.ID from inserted i;
select @emp_name=i.Name from inserted i;
select @emp_empadd =i.empAdd from inserted i;
select @emp_Mob = i.Mobile from inserted i;
SET @audit_action='Updated Record ,trg_Emp_update.';
BEGIN

 SET @str = 'Employee ID '+ CAST( @emp_id as VARCHAR) + ' is updated on '+ cast(getdate() as varchar) +' Employee Name : ' + @emp_name
                                                + 'Employee Add : ' + @emp_empadd + ' Mobile : '+ @emp_Mob + '   ' + @audit_action;

Insert into tblAuditEmp (Audit_Desc) values (@str);
END

Trigger Insert
Create trigger trg_Emp_insert_Instead_of on EMP
Instead of insert
AS
declare @emp_id int, @emp_name varchar(55), @emp_empadd varchar(100),
        @emp_Mob varchar(100), @audit_action varchar(100),@str VARCHAR(1000);
select @emp_id=i.ID from inserted i;
select @emp_name=i.Name from inserted i;
select @emp_empadd =i.empAdd from inserted i;
select @emp_Mob = i.Mobile from inserted i;
SET @audit_action='Inserted Record ,trg_Emp_insert.';
BEGIN

 SET @str = 'Employee ID '+ cast(@emp_id as varchar) + ' is inserted on '+ cast(getdate() as varchar) +' Employee Name : ' + @emp_name
                                                + 'Employee Add : ' + @emp_empadd + ' Mobile : '+ @emp_Mob + '   ' + @audit_action;

Insert into tblAuditEmp (Audit_Desc) values (@str);
END



4 comments:

  1. Thanks for sharing valuable information. Your blogs were helpful to tableau learners. I
    request to update the blog through step-by-step. Also, find the tableau news at
    Tableau Online Course Blog.

    ReplyDelete
  2. Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time
    sql dba training
    sql server dba online training

    ReplyDelete
  3. Through this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing on Msbi online training
    Msbi online training Hyderabad
    Msbi online training India
    Msbi online course
    Msbi course
    Msbi training
    Msbi certification training

    ReplyDelete

If you have any doubt, please let me know.

Popular Posts