insert a row in a table wen other table updated

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

insert a row in a table wen other table updated

Author

Topic

vishwakar
Starting Member

4 Posts

Posted - 2010-12-03 : 09:30:08

Pick all the events for these projects and assign to a dummy user Dummy1 or Dummy2 based on legacy S indicator
Legacy S project assign to Dummy1
Non-Legacy S project assign to Dummy2

o For each event that has an owner in each project moved to the dummy users, add a row to the MOVED_PROJECTS_TB filling out the columns as required

i have approached in this way ..can u guide if i am wrong

1) update project_Event_Tb set project_Event_Tb.owner_id = 'DUMMY1' where project_event_tb.project_id in
(select project_tb.project_id from project_tb,
project_event_tb where project_tb.project_id=project_event_tb.project_id and project_tb.project_id in
(select distinct(project_tb.project_id) from project_tb JOIN project_event_tb
ON project_tb.project_id=project_event_tb.project_id where
((project_tb.created_by =< ? > or PROJECT_EVENT_TB.owner_id =< ?>) and project_tb.PROJ_STATUS in ('OPEN')))
and project_tb.legacys_ind in ('Y','S')
)

2)
update project_Event_Tb set project_Event_Tb.owner_id = 'DUMMY2' where project_event_tb.project_id in
(select project_tb.project_id from project_tb,
project_event_tb where project_tb.project_id=project_event_tb.project_id and project_tb.project_id in
(select distinct(project_tb.project_id) from project_tb JOIN project_event_tb
ON project_tb.project_id=project_event_tb.project_id where
((project_tb.created_by =< ? > or PROJECT_EVENT_TB.owner_id =< ?>) and project_tb.PROJ_STATUS in ('OPEN')))
and project_tb.legacys_ind ='N'
)

insert into moved_projects_tb (project id ,service type, activity type ,EVENT ID, OWNER ROLE ID, MOVED_IND (default to ‘N’),OWNER ATT UID ,last name,first name)values
( Select PROJECT_ID ,SERVICE_TYPE,ACTIVITY_TYPE from project_tb ),
( Select EVENT_ID ,ROLE_ID ,OWNER_ID from project_event_tb),
( select FIRST_NAME,LAST_NAME from contact_tb)
where project_id in
(select distinct(project_tb.project_id) from project_tb JOIN project_event_tb
ON project_tb.project_id=project_event_tb.project_id where
((project_tb.created_by =< ? > or PROJECT_EVENT_TB.owner_id =< ?>) and project_tb.PROJ_STATUS in ('OPEN'))))

vishwakar
Starting Member

4 Posts

Posted - 2010-12-03 : 09:32:12

but the way i approached is not the correct way i guess . when the above update statement is executed it should insert a row in to the moved_table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources