SP & Trigger help - SQL Server Forums

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

SP & Trigger help

Author

Topic

Al_423
Starting Member

3 Posts

Posted - 2006-05-11 : 22:53:37

I am a beginner and need help with SP and Trigger. Here is the scenario:
EMP(Eno,sal,Dno);
DEPT(Dno,Mgr,Tot_Sal_Emp);
Dno in EMP is foreign key. Tot_Sal_Emp is sum of all salary of employees from a given department. How do I write SP and trigger so that when new employees are ADDED, DELETED, OR their salaries are updated, the Tot_Sal_Emp in DEPT table reflects the change.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-11 : 23:14:45

if you are using SP to perform insert, update, delete into EMP table, you don't have to use trigger at all. You can handle it in your SP.

-- Insert SP
insert into EMP(Eno, sal, Dno)
select @eno, @sal, @dno

update DEPT
    set   Tot_Sal_Emp = Tot_Sal_Emp + @sal
    where Dno = @dno

-- Update SP
-- subtract from old dept then add to new dept
update d
    set   Tot_Sal_Emp = Tot_Sal_Emp - e.sal
    from DEPT d inner join EMP e
    on   d.Dno = e.Dno
    where e.Eno = @eno

update EMP
    set   sal = @sal,
          Dno = @dno
    where Eno = @eno

update d
    set   Tot_Sal_Emp = Tot_Sal_Emp + @sal
    where Dno = @dno

Al_423
Starting Member

3 Posts

Posted - 2006-05-11 : 23:23:51

Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources