Adding modify and delete to insert trigger

46 views Asked by At

I've been tasked with pushing records from one table (T1) to another (T2). I have the insert portion complete as follows:

CREATE TRIGGER [dbo].[CP_to_TW2] 
   ON  [dbo].[TEST_PROJ] 
   FOR INSERT
AS 
BEGIN
INSERT INTO dbo.TEST_TW (PROJECT_ID,PROJECT_DESC,PROJECT_MANAGER) 
SELECT PROJ_ID,PROJ_ID+PROJ_NAME,PROJECT_MANAGER FROM inserted

END

TEST_PROJ is T1 and TEST_TW is T2. The PROJECT_ID and PROJ_ID columns store the unique IDs. The trigger fires correct and inserts corresponding rows into T2. However, I am unsure how to get modifications made to T1 to show in T2. For example, if the Project manager is updated in T1 it needs to also update in T2. In addition to this, I am unsure how to make that records in T2 are deleted when they are deleted in T1. Any help would be greatly appreciated.

1

There are 1 answers

3
Simone On BEST ANSWER

You can create triggers also for delete or update ops, in update you have deleted table in addition to inserted

CREATE TRIGGER [dbo].[CP_to_TW2] 
   ON  [dbo].[TEST_PROJ] 
   AFTER UPDATE
AS 
BEGIN
UPDATE TEST_TW....

END

CREATE TRIGGER [dbo].[CP_to_TW2] 
   ON  [dbo].[TEST_PROJ] 
   AFTER DELETE
AS 
BEGIN
DELETE FROM dbo.TEST_TW (PROJECT_ID,PROJECT_DESC,PROJECT_MANAGER) 
WHERE xxx in (SELECT xxx FROM deleted)


END