Create trigger and sp Update one table affected to another table..

-------// CREATE A NEW DATABASE ///------------
CREATE DATABASE ilinkchitharan

USE ilinkchitharan
--------------// CREATE TWO TABLES    ////------------
----// TABLE 1 EMPPERSONAL //-------
CREATE TABLE EmpPersonal
(
Emp_No int PRIMARY KEY IDENTITY (100,1),
Emp_Name VARCHAR(20),
Ph int
)

----// TABLE 2 EMPOFFICIAL //-------

CREATE TABLE EmpOfficial
(
Emp_No int FOREIGN KEY REFERENCES EmpPersonal(Emp_No)  IDENTITY (100,1),
Emp_Name VARCHAR(20),
Designation VARCHAR(25)
)
--------------
DROP TABLE EmpPersonal
-------------// INSERTING VALUES TO THE TABLES 1 //---------
INSERT INTO EmpPersonal VALUES('balaji',567345678)
INSERT INTO EmpPersonal VALUES('chitharan',4341354)
INSERT INTO EmpPersonal VALUES('ravi',34455678)
INSERT INTO EmpPersonal VALUES('naga',564353478)
INSERT INTO EmpPersonal VALUES('narenran',657345678)
INSERT INTO EmpPersonal VALUES('madhan',865345678)
INSERT INTO EmpPersonal VALUES('vinoth',234345678)
INSERT INTO EmpPersonal VALUES('basheer',85345678)
INSERT INTO EmpPersonal VALUES('bharath',165345678)

-------------// INSERTING VALUES TO THE TABLES 2 //---------


INSERT INTO EmpOfficial VALUES('balaji','softwareengg')
INSERT INTO EmpOfficial VALUES('chitharan','softwareengg')
INSERT INTO EmpOfficial VALUES('ravi','Tester')
INSERT INTO EmpOfficial VALUES('naga','Tester')
INSERT INTO EmpOfficial VALUES('narenran','Tester')
INSERT INTO EmpOfficial VALUES('madhan','softwareengg')
INSERT INTO EmpOfficial VALUES('vinoth','Tester')
INSERT INTO EmpOfficial VALUES('basheer','softwareengg')
INSERT INTO EmpOfficial VALUES('bharath','softwareengg')


--------
SELECT * INTO TEMP FROM EmployeePersonal
SELECT *FROM EmpPersonal
SELECT *FROM EmpOfficial

----------//  CREATE TRIGGER USING UPDATE TWO TABLES //--------------

ALTER TRIGGER Tupda
ON   EmpOfficial
AFTER UPDATE
AS
BEGIN

    UPDATE J SET J.Emp_Name = I.Emp_Name
     FROM
     inserted I inner join
     EmpPersonal J
     ON
     I.Emp_No = J.Emp_No
END

---
UPDATE EmpOfficial SET Emp_Name='balajisLALGUDI' WHERE Emp_No=100
UPDATE EmpOfficial SET Emp_Name='SCHITHARAN' WHERE Emp_No=101
UPDATE EmpOfficial SET Emp_Name='RAJA' WHERE Emp_No=103
UPDATE EmpOfficial SET Emp_Name='SUBRAMANI' WHERE Emp_No=104

------

--------------// CREATE TRIGGER USING INSERT VALUES IN TO TWO TABLE //--------
CREATE TRIGGER Tinsert
ON   EmpOfficial
AFTER INSERT
AS
BEGIN
    UPDATE J SET J.Emp_Name = I.Emp_Name from inserted I inner join
    EmpPersonal J ON I.Emp_No = J.Emp_No
END

-----------------
INSERT INTO dbo.EmpOfficial
(Emp_Name,Designation)
 values
('sasikumar','Tester')

--------// CREATE STORED PROCEDURE WITH MERGE CONCEPTS   ///---------------
CREATE PROC SP_update
AS
BEGIN
MERGE EmpOfficial AS TARGET
USING  EmpPersonal  AS new  ON  (TARGET.Emp_No=New.Emp_No )  WHEN  matched and 
TARGET.Emp_Name<>New.Emp_Name
THEN  UPDATE  SET
TARGET.Emp_Name=New.Emp_Name 
WHEN  not matched BY TARGET
THEN
INSERT(Emp_Name ) VALUES ( New.Emp_Name );
END
======================
SELECT*FROM EmpOfficial
SELECT*FROM EmpPersonal
UPDATE EmpPersonal set Emp_Name='Chhjjjjjjjjjen'where Emp_No=100
----------------------
Exec dbo.SP_update
=====================
merge EmpOfficial   as target  using  EmpPersonal  as new  on  (target.Emp_No=New.Emp_No )  when  matched and 
target.Emp_Name<>New.Emp_Name or
target.Designation<>New.Designation or
 then  update  set
target.Emp_Name=New.Emp_Name ,
target.Designation=New.Designation ,
 when  not matched by target then  insert(
Emp_No ,
Emp_Name ,
Designation ,
) Values (
New.Emp_No ,
New.Emp_Name ,
New.Designation ,
 );

 -------------------------------