Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2013
    Posts
    4

    Unanswered: Creating Triggers

    EMP TABLE or EMP_AUDIT([ENO] [numeric](4, 0) NOT NULL,[ENAME] [varchar](10) NOT NULL,[SAL] [numeric](7, 2) NOT NULL,[COMM] [numeric](7, 2) NULL,[DNO] [numeric](2, 0) NULL,[DNAME] [varchar](20) NULL

    EP Table or DNO_Audit:([DEPTNO] [numeric](3, 0) NOT NULL,[DNAME] [varchar](20) NOT NULL,[LOC] [varchar](13) NULL,[Tot_EMPS] [numeric](3, 0) NULL,[Tot_SALs] [numeric](10, 2) NULL

    For Update of the EMP table:

    For Delete from the EMP table:When an employee is deleted from the system, you need to update the tot_Sals and tot_emps in the DEPT table

    When the salary of an employee is updated, a trigger compares the new salary and the old salary. The trigger only performs the update if the new salary is more than the old salary. In that case, the trigger updates the Tot_Sals in DEPT table – otherwise, no action is taken, the information is audited, and the update is undone. (You will use function you have written to check for this. )

    The trigger also checks if DNAME has been updated. • If DNAME is changed

    – If the new department name is not in the department table, then it audits

    the employee that is being updated.

    If the new department is ‘Sales’ then proper commission ($300) is

    entered into the commission field for the employee

    – It will update tot_sals and tot_emps for both the old department and the

    new department for the employee
    Last edited by Pat Phelan; 10-11-13 at 17:02.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What have you tried so far?
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2013
    Posts
    4

    tried

    BEFORE INSERT ON DEPT
    FOR EACH ROW
    WHEN (SQL PROCEDURE TO CHECK UNIQUENESS OF DNO COUNT > 1)
    BEGIN
    INSERT INTO DEPT(department) values (department name)
    END;
    WHEN (SQL PROCEDURE TO CHECK UNIQUENESS OF DNO COUNT = 1)
    BEGIN
    INSERT INTO audit(department) values (department name)
    END;

    but it doesn't do anything

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by MahiSan View Post
    (You will use function you have written to check for this. )
    Did you just copy/paste your homework assignment verbatim?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2013
    Posts
    4
    No 2nd one I tried, I been changing the codes with different scenario to make it work, I tried using if else statements as well but doesn't produce desired results
    original problem is a verbatim of my homework, 2nd one is the idea I m using with different statements

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    FOR EACH ROW sounds like Oracle syntax, rather than SQL Server syntax. What are you using in the class?

  7. #7
    Join Date
    Oct 2013
    Posts
    4
    we are using both, oracle and sql

  8. #8
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    SQL is a set-based language. It is declarative language, so we hate procedural coding. You have more NULLs in one table than I would have in an entire schema.

    A table models a set, NOT a single record. Thus the correct name for a table is a collective noun. We do NOT use numeric(s, p) for identifiers; you do NOT use them for math!

    You have not read the ISO-11179 rules for data element names. Your non-table have no keys. Salary and departments are not attributes of an employee! They have a relationship that you failed to model.

    CREATE TABLE Personnel
    (emp_nbr CHAR(4) NOT NULL PRIMARY KEY,
    emp_name VARCHAR(10) NOT NULL);

    CREATE TABLE Departments
    (dept_nbr CHAR(2) NOT NULL PRIMARY KEY
    dept_name VARCHAR(20) NOT NULL);

    CREATE TABLE Job_Assignments
    (emp_nbr CHAR(4) NOT NULL
    REFERENCES Personnel(emp_id)
    ON UPDATE CASCADE,
    dept_nbr CHAR(2) NOT NULL
    REFERENCES Departments(dept_nbr)
    ON UPDATE CASCADE,
    PRIMARY KEY (emp_id, dept_nbr),
    salary_amt DECIMAL(7,2) NOT NULL,
    commission_amt DECIMAL(7,2));

    >> for delete from the Personnel table: when an employee is deleted from the system, you need to update the salary_amt_tot and personnel_cnt in the dept table*<<

    NO!! Those are computed values that given in a VIEW. The VIEW is always current and does not waste time writing to disk. You are still programming 1950's magnetic tape files, not SQL. We had to punch holes in cards and write bytes to tape back in those days. Just like you are still doing over fifty years later.

    CREATE VIEW Salary_Totals (depart_nbr, salary_amt_tot, personnel_cnt)
    AS
    SELECT dept_nbr, SUM(salary_amt), COUNT(emp_nbr)
    FROM Job_Assignments
    GROUP BY dept_nbr;

    >> when the salary of an employee is updated, a trigger compares the new salary and the old salary. The trigger only performs the update if the new salary is more than the old salary. <<

    Think how silly that requirement is! This is how we did updates with magnetic tapes. The optimizer will handle this in an UPDATE or MERGE statement for you.

    >> In that case, the trigger updates the salary_amt_tot in dept table – otherwise, no action is taken, the information is audited, and the update is undone. (you will use function you have written to check for this.)*<<

    The total is at a different level of aggregation; it soudl not be in the department table at all!

    >> the trigger also checks if department_name has been updated. • if department_name is changed*– if the new department name is NOT in the department table, then it audits*the employee that is being updated.*<<

    NO! DRI actions will prevent this. That is one of many, many ways SQL is not a magnetic tape file system.

    >> If the new department is ‘sales’ then proper commission ($300) is*
    entered into the commission field [sic] for the employee*– it will update salary_amt_tot and perosnnel_cnt for both the old department and the*new department for the employee <<

    Wrong again. Look at how a VIEW works. And you do not even know that columns in a table are nothing like the fields in your magnetic tape files. Wrong terms come from the wrong mindset.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •