Implementation of Trigger

Introduction

In this article lets see how triggers work. A Trigger is a special type of stored procedure that fires when we perform modification on specific data of the specific table.

After trigger is the type of trigger
These triggers run after an insert, update or delete on a table
AFTER TRIGGERS can be classified further into three types as:

(a) AFTER INSERT Trigger.
(b) AFTER UPDATE Trigger.
(c) AFTER DELETE Trigger. 

(a)AFTER INSERT Trigger.

Create table "state"

CREATE TABLE [dbo].[state]
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[State_name] [varchar](50) NULL,
)

Insert data into state table and create another log table for this state table

CREATE TABLE [dbo].[triggerstate](
	[ID] [int] NOT NULL,
	[State_name] [varchar](50) NULL,
	[status] [varchar](50) NULL,
	[timestamp] [varchar](50) NULL
)

Open Stored Procedure from programmability section in Object Explorer in SqlServer Enterprise Manager.

Write below trigger for table state

CREATE trigger statetrigger on state
for insert
	as
	declare @trid int; 
	declare @trState_name varchar(50);
        declare @trstatus varchar (50);
    
select @trid=i.ID from inserted i;
select @trState_name=i.State_name from inserted i;
set @trstatus='insert fired';

insert into triggerstate(ID, State_name, status, timestamp)values(@trid, @trState_name, @trstatus, getdate() );
print 'added'
GO

Now check this by inserting some value in table state you will get the notification in log table i.e. statetrigger
then do the same procedure for delete command and write the following trigger for this

CREATE trigger statetrigger on state
after delete
as
	
   
         declare @trid int; 
	declare @trState_name varchar(50);
        declare @trstatus varchar (50);
    
        select @trid=i.ID from inserted i;
        select @trState_name=i.State_name from inserted i;
        set @trstatus='record deleted';

insert into triggerstate(ID, State_name, status, timestamp)values(@trid, @trState_name, @trstatus, getdate() );
   
GO

CREATE trigger updatelogintrig on login
for update 
as
	
        declare @trid int; 
	declare @trState_name varchar(50);
        declare @trstatus varchar (50);
    
        select @trid=i.ID from inserted i;
        select @trState_name=i.State_name from inserted i;
        set @trigstatus='record updated';    
    
    if update(State_name)
    set @trigstatus='State_name updated';

    
   insert into triggerstate(ID, State_name, status, timestamp)values(@trid, @trState_name,      @trigstatus, getdate() );    
print 'after update trigger fired'
GO

Conclusion

In this way you can get notification for each dml command and can maintain log for each operation

ankit saxena