none
Number of employees in a department of a branch

    Question

  • Hi I am having a few problems trying to complete this project and need some help.

    I need to create an after trigger that displays/prints the number of employees in a department and branch after a row is inserted and then returns the counted number of employees to the department and branch table. Thanks


    Brno
    city
    Noofemployee
    1001
    London
     1
    Branch table
     
    Dept table
    dpno
    dname
    Nonemployee
    2001
    IT
    1
    2002
    MARKETING
    1
     
    Employee table
    eid
    ename
    job
    dpno
    brno
    82986350
    kore
    Data Analyst
    2001
    1001
    82986351
    yahi
    sales
    2002
    1001

    Thursday, March 27, 2014 7:46 PM

Answers

  • Try

    create trigger trEmployeesInsert on Employee AFTER INSERT AS BEGIN ;merge dept as Target using (select count(*) as cntNew, dpNo FROM Inserted GROUP BY dpNo) as Source on Target.dpNo = Source.dpNo WHEN Matched

    THEN UPDATE SET NonEmployee = NonEmployee + Source.cntNew; ;merge Branch as Target using (select count(*) as cntNew, BrNo FROM Inserted GROUP BY BrNo) as Source on Target.BrNo = Source.BrNo WHEN Matched

    THEN UPDATE SET NoOfEmployee = NoOfEmployee + Source.cntNew;



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Kelvinlisa Sunday, March 30, 2014 7:10 PM
    Thursday, March 27, 2014 8:17 PM
    Moderator

All replies

  • use below code and see, if it works for you...

    CREATE TABLE dbo.Employee
    (
    Eid INT,
    Ename VARCHAR(10),
    Job VARCHAR(10),
    Deptno INT,
    Brno INT
    )
    GO
    
    CREATE TRIGGER trg_Insert
    ON dbo.Employee AFTER INSERT
    AS
    BEGIN
    
    SELECT deptno, COUNT(brno) AS Count_of Branch
    FROM Employee
    GROUP BY deptno
    
    END
    GO
    
    INSERT INTO Employee VALUES(1, 'a','b', 1, 1), (2, 'b','c', 1, 2)
    GO
    
    DROP TABLE Employee
    GO

    Thursday, March 27, 2014 7:57 PM
  • Try

    create trigger trEmployeesInsert on Employee AFTER INSERT AS BEGIN ;merge dept as Target using (select count(*) as cntNew, dpNo FROM Inserted GROUP BY dpNo) as Source on Target.dpNo = Source.dpNo WHEN Matched

    THEN UPDATE SET NonEmployee = NonEmployee + Source.cntNew; ;merge Branch as Target using (select count(*) as cntNew, BrNo FROM Inserted GROUP BY BrNo) as Source on Target.BrNo = Source.BrNo WHEN Matched

    THEN UPDATE SET NoOfEmployee = NoOfEmployee + Source.cntNew;



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Kelvinlisa Sunday, March 30, 2014 7:10 PM
    Thursday, March 27, 2014 8:17 PM
    Moderator
  • Hi I am having a few problems trying to complete this project and need some help.

    I need to create an after trigger that displays/prints the number of employees in a department and branch after a row is inserted and then returns the counted number of employees to the department and branch table. Thanks

    Trigger should be the last resort.

    How about a stored procedure with email?  Scheduled as a job?

    sqlsaga

    You cannot have a SELECT in a trigger.


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    Thursday, March 27, 2014 8:32 PM
    Moderator