Changing the view inside my code, can't add row multipls at time

Question

Create a view named VDept_Headcount that reports headcount for each department. The report includes 3 columns as follow: Dept_Name, Dept_Number, No_Emp. Include all the departments. Show the content of the view through SQL (Select * from VDept_Headcount;)

USE COMPANY
GO

--1-1) Create a view named VDept_Headcount that reports headcount for each department.
CREATE VIEW VDept_Headcount(Dept_Name, Dept_Number, No_Emp)
AS
SELECT d.DNAME, d.DNUMBER, COUNT(e.DNO) 
FROM DEPARTMENT d LEFT OUTER JOIN EMPLOYEE e ON e.DNO = d.DNUMBER
GROUP BY d.DNAME, d.DNUMBER;
GO

SELECT *
FROM VDept_Headcount;



USE COMPANY

GO

--Add yourself into Employee
INSERT INTO EMPLOYEE
VALUES('Dan', NULL, 'Chan', '122345687', '13-Nov-1948', '323 Williams Ct, Columbus, OH', 'M', 55000, '987654321', 5);

SELECT *
FROM VDept_Headcount;

Basically I want to know is there a way to add row without using groupon?

score 2 · Answer 1 · edited Jul 29 '20 at 18:27

You really don't want to add a cursor to your trigger. At no point in your SQL Developing life should you ever think that you want to add a cursor to anything, especially a trigger. Sometimes you might have to use a cursor, but you shouldn't set out to solve any task with the mindset that you are going to use a cursor. First stop is a set based solution, then if is not possible, or has performance issues, then explore an RBAR (Row by agonising Row) approach.

Rant over. You can achieve this for all actions (INSERT, DELETE, UPDATE) using:

INSERT dbo.Audit_Emp_Record (date_of_change, old_Lname, new_Lname, old_ssn, new_ssn, old_dno, new_dno)
SELECT  GETDATE(),
        d.Lname,
        i.LName,
        d.SSN,
        i.SSN,
        d.Dno.
        i.Dno
FROM    inserted AS i
        FULL JOIN deleted AS d
            ON i.EmployeeID = d.EmployeeID; -- OR WHATEVER YOUR PK IS

N.B. It is a good idea to always list the columns you are inserting to, this will future proof your code if any columns are added later on.

Changing the view inside my code, can't add row multipls at time

1 Answers1