sql trigger Insert,update Example

sql trigger Insert,update Example :

 

 - - Create Table - -

Create Table Employee

(

ID  Int Primary Key Identity(1,1),

Name Varchar(100),

Occuption Varchar(100),

Address Varchar(100),

Salary Decimal

)

- - Sub Table - -

Trigger Fire This Table Insert

Create Table Employee_Trig

(

ID  Int Primary Key Identity(1,1),

Name Varchar(100),

Occuption Varchar(100),

Address Varchar(100),

Salary Decimal,

       EmpID Int

)

 - - Create Trigger - -

Syn

Create Trigger TriggerName

ON TableName For Insert,Update,Delete 

AS

BEGIN

     .....

      ...

END

---

 - - InsertTrigger - -

Create Trigger Trig_Employee

ON Employee FOR Insert           -->Insert 

AS

DECLARE 

@ID Int,

@Name Varchar(100),

@Occuption Varchar(100),

@Address Varchar(100),

@Salary Decimal

BEGIN

Select @ID=i.ID From Inserted i

Select @Name=i.Name From Inserted i

Select @Occuption=i.Occuption From Inserted i

Select @Address=i.Address From Inserted i

Select @Salary=i.Salary From Inserted i

Insert Into Employee_Trig(EmpID,Name,Occuption,Address,Salary)

Values(@ID,@Name,@Occuption,@Address,@Salary)

END

Insert Into Employee(Name,Occuption,Address,Salary)

Values('aaa','Dev','chennai',1000)

Select * From Employee_Trig

 - - Update  Trigger - -

Alter Trigger Trig_Employee_Update

ON Employee FOR Update               - ->Update Trigger           

AS

DECLARE 

@ID Int,

@Name Varchar(100),

@Occuption Varchar(100),

@Address Varchar(100),

@Salary Decimal

BEGIN

Select @ID=i.ID From Inserted i

Select @Name=i.Name From Inserted i

Select @Occuption=i.Occuption From Inserted i

Select @Address=i.Address From Inserted i

Select @Salary=i.Salary From Inserted i

Insert Into Employee_Trig(EmpID,Name,Occuption,Address,Salary)

Values(@ID,@Name,@Occuption,@Address,@Salary)

END

Update Employee Set Name='vijay' Where ID=2

Select * From Employee

Select * From Employee_Trig

-- Trigger Fired Table --