Updating salary for different departments using cursor vibeline dating
Note: Columns in RED color indicate primary key(s).INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID) VALUES (207, 'ANGELA', 'SNYDER','ANGELA','2', SYSDATE, 'SA_MAN', 12000, 80) SELECT JOB_TITLE, DEPARTMENT_NAME, LAST_NAME, START_DATE FROM JOB_HISTORY JOIN JOBS USING (JOB_ID) JOIN DEPARTMENTS USING (DEPARTMENT_ID) JOIN EMPLOYEES USING (EMPLOYEE_ID) WHERE TO_CHAR(START_DATE,'YYYY') BETWEEN 20 SELECT TO_CHAR(HIRE_DATE,'MON-YY') FROM EMPLOYEES JOIN DEPARTMENTS USING (DEPARTMENT_ID) JOIN LOCATIONS USING (LOCATION_ID) WHERE CITY = 'Seattle' GROUP BY TO_CHAR(HIRE_DATE,'MON-YY') HAVING COUNT(*) SELECT COUNTRY_NAME, CITY, COUNT(DEPARTMENT_ID) FROM COUNTRIES JOIN LOCATIONS USING (COUNTRY_ID) JOIN DEPARTMENTS USING (LOCATION_ID) WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING COUNT(DEPARTMENT_ID) Declare V_salary_120 employees.salary%type; Begin Select salary into v_salary_120 From employees where employee_id = 120; Update employees set salary = ( select salary from employees where employee_id = 122) Where employee_id = 120; Update employees set salary = v_salary_120 Where employee_id = 122; Commit; End; declare v_salary employees.salary%type; v_exp number(2); v_cp number(5,2); begin select v_salary, floor ( (sysdate-hire_date)/365) into v_salary, v_exp from employees where employee_id = 150; if v_salary declare v_name employees.first_name%type; v_deptname departments.department_name%type; begin select first_name , department_name into v_name, v_deptname from employees join departments using (department_id) where employee_id = ( select manager_id from employees where employee_id = 103); dbms_output.put_line(v_name); dbms_output.put_line(v_deptname); end; declare v_min number(3); v_max number(3); v_c number(1); begin select min(employee_id), max(employee_id) into v_min, v_max from employees; for i in v_min 1 ..When the trigger fires, the declarative part executes before any timing-point sections execute.Variables and subprograms declared in this section have firing-statement duration.For example, when you transfer an employee from department 1 to department 2 and give the employee a promotion on the same day, users with access to either department 1 or 2 have access to the employee's data because the transfer data row contains an effective-dated sequence number.You can prevent this access by changing the security views for the People Soft applications that you use.When you transfer people from one department to another by using a data row that contains an effective-dated sequence number, the system currently allows users with security access to the old or new department to have access to all the job data.The system is delivered this way because implementing security in system views that are specific to the function Max (effseq) on PS_JOB would slow down online response time.
(Ensure that you position the cursor on the data row that you want to copy before you insert the new row.) The only new information is the effective date, which is set by default to the system date (usually today's date).
The solution to mutating problem is explained at : Work around for mutating problem in Oracle Triggers. create or replace trigger trg_log_job_change after update of job_id on employees for each row declare v_enddate date; v_startdate date; begin -- find out whether the employee has any row in job_history table select max(end_date) into v_enddate from job_history where employee_id = :old.employee_id; if v_enddate is null then v_startdate := :old.hire_date; else v_startdate := v_enddate 1; end if; insert into job_history values (:old.employee_id, v_startdate, sysdate, :old.job_id, :old.department_id); end; Note: Before testing the above trigger, you need to disable UPDATE_JOB_HISTORY trigger, which is already present in HR account, as it does the same.
And I need to create 1 trigger that checks if the department number of the updated worker, the total salary between all the workers of that department doesn't exceeds 10000, but I don't know how to do it really... CONTROLSALARIDEPT", line 14 ORA-04088: error during execution of trigger 'SPECIAL.
For more information, see Using Compound Triggers to Avoid Mutating-Table Error.) Q: What is a compound trigger and how does it work ?
A: This is a huge topic, please refer to the documentation here: In short: this is a special kind of a trigger that makes psiible to combine four types of separate triggers: The optional declarative part (the first part) declares variables and subprograms that timing-point sections can use.You can work only with current and future job data on the Current Job pages. Or you can plan ahead and set up tables and data before they take effect.The Job Data and Current Job components are made up of the same pages as the Add Employment Instance, Add Contingent Worker Instance, and Add a POI (person of interest) Relationship components. The system also uses effective dates to compare pages and tables to ensure that the prompt tables that you see list only data that is valid as of the effective date of the current page.CREATE OR REPLACE TRIGGER compound_trigger_name FOR INSERT OR UPDATE OF salary ON treballa COMPOUND TRIGGER TYPE Departments_t IS TABLE OF treballa.department%TYPE INDEX BY varchar2(100); Departments Departments_t; BEFORE EACH ROW IS BEGIN -- collect updated or inserted departments Departments( :new.department ) := :new.department; END BEFORE EACH ROW; AFTER STATEMENT IS sum_sal NUMBER; BEGIN -- for each updated department check the restriction FOR dept IN Departments. The session that issued the triggering statement cannot query or modify a mutating table.