SET SERVEROUTPUT ON;

DROP TABLE emp;
CREATE TABLE emp
( matr NUMBER(10) NOT NULL,
  nom VARCHAR2(50) NOT NULL,
  sal number(7,2),  
  adresse VARCHAR2(96),
  dep NUMBER(10) NOT NULL,
  CONSTRAINT emp_pk PRIMARY KEY (matr)
);

INSERT INTO EMP VALUES (0,'Turing, Alan', 2446.13, 'Manchester, GB', 1);
INSERT INTO EMP VALUES (1,'Dijkstra, Edsger', 1779.88, 'Rotterdam, NL', 1);
INSERT INTO EMP VALUES (2,'Mac Lane, Saunders', 3141.16, 'Chicago, US', 2);

SELECT * FROM emp;

------------------------------------------------------------------------------------------------------------------------
-- création de la table pour le logging

DROP TABLE log_emp;
CREATE TABLE log_emp
( matr NUMBER(10) NOT NULL,
  nom VARCHAR2(50) NOT NULL,
  sal number(7,2),  
  adresse VARCHAR2(96),
  dep NUMBER(10) NOT NULL,
  loggin VARCHAR2(50),
  date_m DATE,
  action VARCHAR2(10)
);

SELECT *
from log_emp;

SELECT *
from emp;

------------------------------------------------------------------------------------------------------------------------
-- trigger logging on insert

CREATE or REPLACE TRIGGER logadd
  AFTER INSERT
  ON emp
  FOR EACH ROW
BEGIN
  INSERT INTO log_emp VALUES(
    :new.matr,
    :new.nom,
    :new.sal,
    :new.adresse,
    :new.dep,
    USER,
    SYSDATE,
    'INSERT');
END;

INSERT INTO EMP VALUES (3,'Tarjan, Robert', 2446.13, 'Princeton, US', 2);
SELECT * FROM emp;
SELECT * FROM log_emp;
-- remarque : on ne loggue UNIQUEMENT les inserts.
-- Il faudrait aussi enregistrer les deletes et les updates


------------------------------------------------------------------------------------------------------------------------
-- triggers conditionnels
CREATE OR REPLACE TRIGGER t_emp_sal_increase
  BEFORE UPDATE OF sal, dep -- ici on regarde les updates sur deux attributs
  ON EMP
  FOR EACH ROW
  WHEN (NEW.dep = 1)
BEGIN
  IF UPDATING('sal') THEN
    IF (:NEW.sal < :OLD.sal)  THEN
      raise_application_error(-20000,'Sallary cannot decrease');
    END IF;
  END IF;
END;

UPDATE emp
SET sal = sal-1000
WHERE matr = 1; --dep = 1

UPDATE emp
SET sal = sal-1000
WHERE matr = 3; --dep = 2


------------------------------------------------------------------------------------------------------------------------
-- probleme des tables mutantes

CREATE OR REPLACE TRIGGER emp_count
  AFTER DELETE ON emp
  FOR EACH ROW
  DECLARE
    n integer;
BEGIN
  SELECT COUNT(*) INTO n
  FROM emp;
  dbms_output.put_line(
    'On a ' ||  n || ' employes dans la base');
END;
/

DELETE FROM emp WHERE matr = 3;

-- SQL Error: ORA-04091: table RTHION.EMP is mutating, trigger/function may not see it
-- ORA-06512: at "RTHION.EMP_COUNT", line 4
-- ORA-04088: error during execution of trigger 'RTHION.EMP_COUNT'
-- 04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"


DROP TRIGGER t_emp_sal_increase;
DROP TRIGGER emp_count;
DROP TRIGGER logadd;

DELETE FROM log_emp;
DELETE FROM emp;
COMMIT;

