uts_plsql_nabila.sql

-- CREATE SEQUENCE hdept_seq START WITH 1;
-- /
-- CREATE TABLE HISTORY_DEPARTMENTS (
--   HISTORY_ID        number DEFAULT hdept_seq.nextval NOT NULL,  
--   DEPARTMENT_ID        number,  
--   DEPARTMENT_NAME          varchar2(100) not null, 
--   constraint pk_hdepartments primary key (HISTORY_ID)
-- );

-- CREATE SEQUENCE dept_seq START WITH 1;
-- /
-- CREATE TABLE DEPARTMENTS (
--   DEPARTMENT_ID        number  DEFAULT dept_seq.nextval NOT NULL,  
--   DEPARTMENT_NAME          varchar2(100) not null,
--   constraint pk_departments primary key (DEPARTMENT_ID)  
-- );

-- INSERT INTO DEPARTMENTS(DEPARTMENT_NAME) VALUES ('MARKETING');
-- INSERT INTO DEPARTMENTS(DEPARTMENT_NAME) VALUES ('HR');
-- INSERT INTO DEPARTMENTS(DEPARTMENT_NAME) VALUES ('IT');

-- SELECT * FROM DEPARTMENTS;


-- UPDATE DEPARTMENTS SET DEPARTMENT_NAME='MARKETING' WHERE DEPARTMENT_ID=1;

-- SELECT * FROM DEPARTMENTS;

create or replace trigger tg_update_department 
before update
on DEPARTMENTS 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
    -- INSERT INTO HISTORY_DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME) VALUES(:OLD.DEPARTMENT_ID, :OLD.DEPARTMENT_NAME);
    :NEW.DEPARMENT_NAME := 'AAAA';
    -- UPDATE DEPARTMENTS SET DEPARTMENTS.DEPARTMENT_NAME = 'aaa' WHERE DEPARTMENTS.DEPARTMENT_ID = :OLD.DEPARTMENT_ID;
END;
/

SELECT * FROM HISTORY_DEPARTMENTS;
SELECT * FROM DEPARTMENTS;

UPDATE DEPARTMENTS SET DEPARTMENT_NAME='MARKETING' WHERE DEPARTMENT_ID=1;