1)CREATE OR REPLACE TRIGGER TRG_Deb_Insert
before INSERT ON Emps
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Un employé va être ajouté');
END;
2) INSERT INTO emps
VALUES (
1000,
'yassine',
'laribi',ccc
24000
);
3) CREATE OR REPLACE TRIGGER TRG_Fin_Insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Fin insertion employé');
END;
INSERT INTO employees
VALUES (
1000,
'yassine',
'laribi',
'laribi@[Link]',
21100,
TO_DATE('17-JUN-1987', 'DD-MON-YYYY'),
'AD_PRES',
24000,
NULL,
NULL,
90
);
4) CREATE OR REPLACE TRIGGER ahhhhhh
BEFORE UPDATE ON emps
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Old Salary: ' || :[Link]);
DBMS_OUTPUT.PUT_LINE('New Salary: ' || :[Link]);
END;
5 DECLARE
v_id INT := 200;
v_salary INT;
BEGIN
LOOP
SELECT salary INTO v_salary FROM emps WHERE employee_id = v_id;
UPDATE emps
SET salary = v_salary + (v_salary * 15 / 100)
WHERE employee_id = v_id;
DBMS_OUTPUT.PUT_LINE(v_id);
EXIT WHEN v_id = 205;
v_id := v_id + 1;
END LOOP;
END;
6)
CREATE OR REPLACE TRIGGER ondelete
BEFORE delete on emps
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('employee number: ' || :OLD.EMPLOYEE_ID ||'employee name: '
|| :OLD.first_name);
END;
delete from emps where employee_id=205;
7)
CREATE OR REPLACE TRIGGER lala
BEFORE DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('Inserting');
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('Updating');
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE('Deleting');
END IF;
END;
8)
UPDATE employees SET first_name = 'will delete' WHERE employee_id = 204;
DELETE FROM employees WHERE employee_id = 202;
INSERT INTO employees
VALUES (
202,
'yassine',
'laribi',
'email@',
'20120155',
TO_DATE('17-JUN-1987', 'DD-MON-YYYY'),
'AD_PRES',
24000,
NULL,
NULL,
20
);
9)
CREATE TABLE Journal (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
operation_type VARCHAR2(10),
employee_id NUMBER,
operation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE OR REPLACE TRIGGER trg_emp_insert
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
INSERT INTO Journal (operation_type, employee_id)
VALUES ('INSERT', :NEW.EMPLOYEE_ID); -- Use EMPLOYEE_ID instead of id
END;
CREATE OR REPLACE TRIGGER trg_emp_update
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
INSERT INTO Journal (operation_type, employee_id)
VALUES ('UPDATE', :NEW.EMPLOYEE_ID); -- Use EMPLOYEE_ID instead of id
END;
10)
11)
CREATE OR REPLACE TRIGGER TRG_dept_Delete
BEFORE DELETE ON departments
FOR EACH ROW
BEGIN
DELETE FROM employees
WHERE department_id = :OLD.department_id;
END;
12)
13)
CREATE OR REPLACE TRIGGER trg_update_mgr_employees
AFTER UPDATE OF manager_id ON departments
FOR EACH ROW
BEGIN
UPDATE employees
SET manager_id = :NEW.manager_id
WHERE department_id = :OLD.department_id;
END;
14)
CREATE TABLE jobs_copy AS
SELECT * FROM jobs;
15)
CREATE OR REPLACE TRIGGER TRG_Null_Job
AFTER DELETE OR UPDATE OF JOB_ID ON JOBS
FOR EACH ROW
DECLARE
CURSOR emp_cursor IS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM EMPLOYEES
WHERE JOB_ID = :OLD.JOB_ID;
v_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
v_first_name EMPLOYEES.FIRST_NAME%TYPE;
v_last_name EMPLOYEES.LAST_NAME%TYPE;
BEGIN
IF DELETING THEN
-- Mettre à jour les employés avec JOB_ID = :OLD.JOB_ID vers 'NONE'
UPDATE EMPLOYEES
SET JOB_ID = 'NONE'
WHERE JOB_ID = :OLD.JOB_ID;
DBMS_OUTPUT.PUT_LINE('Suppression de la fonction "' || :OLD.JOB_ID || '" détectée.');
DBMS_OUTPUT.PUT_LINE('Les employés suivants ont été mis à jour (JOB_ID = NONE) :');
-- Affichage des employés affectés
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_employee_id, v_first_name, v_last_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('- ' || v_employee_id || ': ' || v_first_name || ' ' || v_last_name);
END LOOP;
CLOSE emp_cursor;
ELSIF UPDATING THEN
-- Mise à jour des employés vers la nouvelle fonction
UPDATE EMPLOYEES
SET JOB_ID = :NEW.JOB_ID
WHERE JOB_ID = :OLD.JOB_ID;
DBMS_OUTPUT.PUT_LINE('Mise à jour de la fonction "' || :OLD.JOB_ID || '" vers "'
|| :NEW.JOB_ID || '".');
DBMS_OUTPUT.PUT_LINE('Les employés suivants ont été mis à jour avec la nouvelle
fonction :');
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_employee_id, v_first_name, v_last_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('- ' || v_employee_id || ': ' || v_first_name || ' ' || v_last_name);
END LOOP;
CLOSE emp_cursor;
END IF;
END;
16)
17)
CREATE OR REPLACE TRIGGER trg_prevent_job_delete
BEFORE DELETE ON jobs
FOR EACH ROW
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM employees
WHERE job_id = :OLD.job_id;
IF v_count > 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Cannot delete job: It is still assigned to one or more
employees.');
END IF;
END;
18) ALTER TRIGGER trg_null_job DISABLE;
19)
SELECT trigger_name
FROM user_triggers
WHERE table_name = 'EMPLOYEES';
ALTER TRIGGER trg_emp_insert DISABLE;
ALTER TRIGGER trg_emp_update DISABLE;
20)
SELECT trigger_name, table_name, status
FROM user_triggers;