trigger in sql(after trigger)

Update one Table after insert or update data into another Table

 

here We Have Two Tables

table1 and table2,and we want to udpate table2 using after trigger

 

Step1

create table1

Create table table1

(

Id1 int Identity  (1,1) Not Null,

EmpCode1 nvarchar(10),

Name1 nvarchar(20)

)

 

 

Step2

create table2

Create table table2

(

Id2 int Identity  (1,1) Not Null,

EmpCode2 nvarchar(10),

Name2 nvarchar(20)

)

 

Step3

create After trigger on table1

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

Create trigger [updatetable2]

on [dbo].[table1]

After insert,update

As

Begin

Set NoCount on

Declare @EmpCode nvarchar(50)

Declare @name nvarchar(50)

set @EmpCode = (select [EmpCode1] from inserted)

set @name = (select [Name1] from inserted)

if not exists (select * from table2 where [EmpCode2] = @EmpCode)

begin

insert into  table2 ([EmpCode2],[Name2])values (@EmpCode,@name)

end

else

begin

update table2 set [Name2] = @name where [EmpCode2] = @EmpCode

end

end

 

 

Step4

insert data into table1 for testing and show the result

insert into table1 (EmpCode1,Name1)values ('001','Aashish')

select * from table1

select * from table2

 

 

Step5

update data into table1 for testing and show affect on table2

update table1 set Name1 = 'Dynamic' where EmpCode1 = '001'

select * from table1

select * from table2
Advertisements
This entry was posted in Sql. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s