Example of a Sample Trigger In PLSQL

Topic Introduction: This tutorial will give an Example of a Sample Function In PLSQL.

Table Create For Data Insert

CREATE TABLE test_data_insert

(

    pid NUMBER,

    pcode VARCHAR2 (40),

    data_type VARCHAR2 (40),

    insert_date DATE,

    insert_year VARCHAR2 (4),

    insert_slno NUMBER,

    remarks VARCHAR2 (500)

);

Trigger Example for Primary Key Generate

CREATE OR REPLACE TRIGGER trg_test_data_insert

    BEFORE INSERT

    ON test_data_insert

    FOR EACH ROW

BEGIN

    IF :new.pid IS NULL

    THEN

        SELECT NVL (MAX (pid), 0) + 1

          INTO :NEW.pid

          FROM test_data_insert;

    END IF;

END;

/

Trigger Example for Primary Key Generate Using Sequence

CREATE OR REPLACE TRIGGER trig_test_datapk

    BEFORE INSERT

    ON test_data_insert

    FOR EACH ROW

BEGIN

    IF :new.pid IS NULL

    THEN

        SELECT seq_test_datapk.NEXTVAL INTO :new.pid FROM DUAL;

    --:new.pid := seq_test_datapk.NEXTVAL;

    END IF;

END;

 

Trigger Example for Code Generate

CREATE OR REPLACE TRIGGER trg_test_data_insert

    BEFORE INSERT

    ON test_data_insert

    FOR EACH ROW

BEGIN

    IF :new.pcode IS NULL

    THEN

        SELECT :new.data_type||'.'||LPAD(NVL(MAX(TO_NUMBER(SUBSTR(pcode ,5,5))),0)+1,5,'0')||'.'||TO_CHAR(TO_DATE(sysdate),'YYYY')

          INTO :NEW.pcode

          FROM test_data_insert;

    END IF;

END;

/