Adding modify and delete to insert trigger

Question

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.

You would do this with an update statement. Something like this help? http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server — Sean Lange, Jun 18 '15 at 13:57

score 2 · Accepted Answer · answered Jun 18 '15 at 13:58

2

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

answered Jun 18 '15 at 13:58

Simone

1,828
1
13
20

Thanks Simone. I am tackling the UPDATE portion first and have the following: ALTER TRIGGER [dbo].[CP_to_TW3] ON [dbo].[TEST_PROJ] AFTER UPDATE AS BEGIN UPDATE dbo.TEST_TW SET TEST_TW.PROJECT_ID = TEST_PROJ.PROJ_ID, TEST_TW.PROJECT_DESC = TEST_PROJ.PROJ_ID+TEST_PROJ.PROJ_NAME, TEST_TW.PROJECT_MANAGER = TEST_PROJ.PROJECT_MANAGER FROM TEST_TW INNER JOIN TEST_PROJ ON TEST_TW.PROJECT_ID = TEST_PROJ.PROJ_ID END It is not firing when I add new fields. Could you point out where I may be going wrong? – AdamTS Jun 18 '15 at 15:31
In the update trigger you have both inserted and deleted; i f=would try something like this ALTER TRIGGER [dbo].[CP_to_TW3] ON [dbo].[TEST_PROJ] AFTER UPDATE AS BEGIN UPDATE dbo.TEST_TW SET TEST_TW.PROJECT_ID = T.PROJ_ID, TEST_TW.PROJECT_DESC = T.PROJ_ID+T.PROJ_NAME, TEST_TW.PROJECT_MANAGER = T.PROJECT_MANAGER FROM TEST_TW INNER JOIN deleted T ON TEST_TW.PROJECT_ID = T.PROJ_ID END. Consider you have to be sure the two tables must share same data to have an update – Simone Jun 18 '15 at 16:08
Ahhh, ok. That makes a lot more sense. Thanks! – AdamTS Jun 18 '15 at 17:29

Adding modify and delete to insert trigger

1 Answers1