Implementation of Trigger

Ankitsrist
Posted by in ASP.NET category on for Beginner level | Points: 250 | Views : 2950 red flag
Rating: 4.5 out of 5  
 2 vote(s)

In this article lets see how to deal with triggers, Triggers is a special type of stored procedure that fires when we perform modification on specific data of the specific table

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
and for update write following trigger

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


Page copy protected against web site content infringement by Copyscape

About the Author

Ankitsrist
Full Name: ankit saxena
Member Level: Starter
Member Status: Member
Member Since: 11/16/2012 11:33:51 PM
Country: India

http://www.dotnetfunda.com
hello, i have done engineering from I.T. branch, recently got placed in endroit technologies mumbai as a trainee engineer its my first company ...i was java developer but during job my interest towards asp.net has emerged :)

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)