Essential PL/SQL Triggers, Procedures, and DDL Examples

Posted on May 19, 2026 in Computing and Communications

Database Triggers for Data Integrity

Create table emp1 and dept1 same as emp and dept. Create a trigger which will delete all records from emp1 table of corresponding department deleted from dept1 table.

CREATE TABLE emp1 AS SELECT * FROM emp;
CREATE TABLE dept1 AS SELECT * FROM dept;

CREATE OR REPLACE TRIGGER trg_delete_emp1
AFTER DELETE ON dept1
FOR EACH ROW
BEGIN
    DELETE FROM emp1 WHERE deptno = :OLD.deptno;
END;
/

Create table totalsal as select deptno, sum(sal) sal from emp1 group by deptno. Write a trigger which will update total salary in totalsal table whenever there is modification in sal of emp1 table.

CREATE TABLE totalsal AS SELECT deptno, SUM(sal) sal FROM emp1 GROUP BY deptno;

CREATE OR REPLACE TRIGGER trg_update_totalsal
AFTER UPDATE OF sal ON emp1
FOR EACH ROW
BEGIN
    UPDATE totalsal
    SET sal = sal - :OLD.sal + :NEW.sal
    WHERE deptno = :OLD.deptno;
END;
/

PL/SQL Blocks and Cursors

Create a table emp1 same as emp. Write a PL/SQL block which will insert all the details of employees in a given department to a new table emp1 (use cursor).

CREATE TABLE emp1 AS SELECT * FROM emp WHERE 1=2;

DECLARE
    CURSOR c1 IS SELECT * FROM emp WHERE deptno = 10;
    v_emp emp%ROWTYPE;
BEGIN
    OPEN c1;
    LOOP
        FETCH c1 INTO v_emp;
        EXIT WHEN c1%NOTFOUND;
        INSERT INTO emp1 VALUES (v_emp.empno, v_emp.ename, v_emp.job, v_emp.mgr, v_emp.hiredate, v_emp.sal, v_emp.comm, v_emp.deptno);
    END LOOP;
    CLOSE c1;
END;
/

Stored Procedures for Salary Management

Create a table emp1 same as emp.

I. Write a procedure to update salary of a given employee to 3000 if salary is less than 3000, else give a 10% increment on emp1 table.
II. Write a PL/SQL procedure which will insert all the details of a given employee with empno to a new empty table emp1_new which has the same structure as emp.

CREATE TABLE emp1 AS SELECT * FROM emp;

CREATE OR REPLACE PROCEDURE update_salary(p_empno NUMBER) IS
    v_sal emp1.sal%TYPE;
BEGIN
    SELECT sal INTO v_sal FROM emp1 WHERE empno = p_empno;
    IF v_sal < 3000 THEN
        UPDATE emp1 SET sal = 3000 WHERE empno = p_empno;
    ELSE
        UPDATE emp1 SET sal = sal + (sal * 0.10) WHERE empno = p_empno;
    END IF;
END;
/

CREATE TABLE emp1_new AS SELECT * FROM emp WHERE 1=2;

CREATE OR REPLACE PROCEDURE insert_emp_details(p_empno NUMBER) IS
BEGIN
    INSERT INTO emp1_new SELECT * FROM emp WHERE empno = p_empno;
END;
/

PL/SQL Functions for Aggregation

Create a table emp1 same as emp.

I. Write a PL/SQL function which will return total salary paid to a given department number in emp1.
II. Write a PL/SQL function which will return the total number of employees working in a given department number in emp1.

CREATE OR REPLACE FUNCTION total_salary(p_deptno NUMBER) RETURN NUMBER IS
    v_total NUMBER;
BEGIN
    SELECT SUM(sal) INTO v_total FROM emp1 WHERE deptno = p_deptno;
    RETURN v_total;
END;
/

CREATE OR REPLACE FUNCTION total_employees(p_deptno NUMBER) RETURN NUMBER IS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM emp1 WHERE deptno = p_deptno;
    RETURN v_count;
END;
/

Managing Database Views

Execute DDL/DML statements demonstrating the use of views:

Create table emp1 same as emp.
Create view dept20 for employees in department 20.
Update emp1 using view dept20.
Delete record using view dept20.
Create view dept30 with check option.
Insert record in view dept30 (demonstrate error).
Create and drop empview.

CREATE TABLE emp1 AS SELECT * FROM emp;
CREATE VIEW dept20 AS SELECT * FROM emp1 WHERE deptno = 20;
UPDATE dept20 SET comm = comm + 100 WHERE comm IS NOT NULL;
DELETE FROM dept20 WHERE empno = 7369;
CREATE VIEW dept30 AS SELECT * FROM emp1 WHERE deptno = 30 WITH CHECK OPTION;
-- INSERT INTO dept30 VALUES (73,'akash','clerk',7902,'17-DEC-1980',800,NULL,20);
CREATE VIEW empview AS SELECT e.empno, e.ename, e.sal, e.deptno, d.dname FROM emp1 e, dept d WHERE e.deptno = d.deptno;
DROP VIEW empview; DROP VIEW dept20; DROP VIEW dept30;

Advanced DDL and Constraints

Perform the following SQL queries on the database:

Add/Delete columns and constraints (Primary Key, Check, Default).
Modify data types.

ALTER TABLE emp1 ADD grade VARCHAR2(10);
ALTER TABLE emp DROP COLUMN grade;
ALTER TABLE emp1 ADD CONSTRAINT EMPNO_P PRIMARY KEY (empno);
ALTER TABLE emp DROP PRIMARY KEY;
ALTER TABLE emp1 ADD CONSTRAINT SAL_C CHECK (sal > 100);
ALTER TABLE emp1 MODIFY sal NUMBER(14,2);
ALTER TABLE student MODIFY ename DEFAULT 'UNKNOWN';
ALTER TABLE emp1 DROP COLUMN sal;