Create trigger Command

Triggers are the database operation that will be fired immediately when a DML statement (INSERT, UPDATE or DELETE) is executed on a database table.

Syntax

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF}

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF col_name]

On table_name

[FOR EACH ROW]

WHEN (Condition)

BEGIN

---SQL Statement---

END


Trigger in SQL

Examples
  • Creating BEFORE INSERT Trigger

    Table

    CREATETABLE Product

    (

    Prod_ID Number(10),

    Prod_Name Varchar2(20),

    Quantity Number(5),

    Price number(5,2),

    Create_datedate,

    Created_by varchar2(10)

    );

    Query

    CREATEOR REPLACETRIGGER Product_before_insert

    BEFORE INSERT

    ON Products

    FOR EACH ROW

    DECLARE

    username varchar2(15);

    BEGIN

    -- Find username of person performing INSERT into table

    SELECTuser INTO username

    FROM dual;

    -- Update create_date field to current system date

    :new.create_date:= systemdate;

    -- Update created_by field to the username of the person performing the INSERT

    :new.created_by:= username;

    END;

  • Creating BEFORE UPDATE Trigger

    Table

    CREATETABLE Product

    (

    Prod_ID Number(10),

    Prod_Name Varchar2(20),

    Quantity Number(5),

    Price number(5,2),

    Updated_datedate,

    Updated_by varchar2(10)

    );

    Query

    CREATEOR REPLACETRIGGER Products_before_update

    BEFORE UPDATE

    ON Products

    FOR EACH ROW

    DECLARE

    username varchar2(15);

    BEGIN

    --To search username of person performing UPDATE on the table

    SELECTuser INTO username

    FROM dual;

    -- Update updated_date field to current system date

    :new.updated_date:= systemdate;

    -- Update updated_by field to the username of the person performing the UPDATE

    :new.updated_by:= username;

    END;

  • Creating BEFORE DELETE Trigger

    Table

    CREATETABLE Product

    (

    Prod_ID Number(10),

    Prod_Name Varchar2(20),

    Quantity Number(5),

    Price number(5,2)

    );

    Query

    CREATEOR REPLACETRIGGER Products_before_delete

    BEFORE DELETE

    ON Products

    FOR EACH ROW

    DECLARE

    username varchar2(15);

    BEGIN

    SELECTuser INTO username

    FROM dual;

    INSERTINTO Products_audit

    ( Prod_ID,

    Prod_Name,

    Quantity,

    Price,

    delete_date,

    deleted_by )

    VALUES

    (:old.Prod_ID,

    :old.Prod_Name,

    :old.Quantity,

    :old.Price,

    systemdate,

    username);

    END;

  • Creating AFTER INSERT Trigger

    Table

    CREATETABLE Product

    (

    Prod_ID Number(10),

    Prod_Name Varchar2(20),

    Quantity Number(5),

    Price number(5,2)

    );

    Query

    CREATEOR REPLACETRIGGER Products_after_insert

    AFTERINSERT

    ON Products

    FOR EACH ROW

    DECLARE

    username varchar2(15);

    BEGIN

    SELECTuser INTO username

    FROM dual;

    INSERTINTO Products_audit

    ( Prod_ID,

    Prod_Name,

    Quantity,

    Price,

    username )

    VALUES

    (:new.Prod_ID,

    :new.Prod_Name,

    :new.Quantity,

    :new.Price,

    username);

    END;

  • Creating AFTER UPDATE Trigger

    Table

    CREATETABLE Product

    (

    Prod_ID Number(10),

    Prod_Name Varchar2(20),

    Quantity Number(5),

    Price number(5,2)

    );

    Query

    CREATEOR REPLACETRIGGER Products_after_update

    AFTERUPDATE

    ON Products

    FOR EACH ROW

    DECLARE

    username varchar2(15);

    BEGIN

    SELECTuser INTO username

    FROM dual;

    INSERTINTO Products_audit

    ( Prod_ID,

    Price_before,

    Price_after,

    username )

    VALUES

    (:new.Prod_ID,

    :old.Price,

    :new.Price,

    username);

    END;

  • Creating AFTER DELETE Trigger

    Table

    CREATETABLE Product

    (

    Prod_ID Number(10),

    Prod_Name Varchar2(20),

    Quantity Number(5),

    Price number(5,2)

    );


    Query

    CREATEOR REPLACETRIGGER Products_after_delete

    AFTERDELETE

    ON Products

    FOR EACH ROW

    DECLARE

    username varchar2(15);

    BEGIN

    SELECTuser INTO username

    FROM dual;

    INSERTINTO Products_audit

    ( Prod_ID,

    Prod_Name,

    Quantity,

    Price,

    delete_date,

    deleted_by)

    VALUES

    (:old.Prod_ID,

    :old.Prod_Name,

    :old.Quantity,

    :old.Price,

    systemdate,

    username);

    END;

  • Creating INSTEAD OF INSERT, UPDATE and DELETE Trigger

    Syntax

    CREATE [ OR REPLACE] TRIGGER trihgger_name

    INSTEADOF

    event1 [OR event2 OR event3]

    ON view_name

    [REFERENCING OLD AS old | NEW AS new]

    [FOR EACH ROW]

    trigger_body

    Table

    CREATETABLE Emp AS

    SELECT Emp_id, name, salary, depart_id, email, job_id

    FROM Employees;

    CREATETABLE depart AS

    SELECT d,depart_id, d.depart_name, d. location_id,sum(e.salary) tot_dept_sal

    FROM Employees e, department d

    WHERE e.depart_id = d.depart_id

    GROUPBY d.depart_id, d. depart_name, d. lpcation_id;

    View

    CREATEVIEW Emp_details AS

    SELECT e.EMP_id, e.name, e.salary, e.depart_id, e.email, e.job_id, d.depart_name,

    d.location_id

    FROM Employees e, department d

    WHERE e.depart_id = d.depart_id;

    Example

    CREATEOR REPLACETRIGGER NEW_Emp_depart

    INSTEADOF INSERTOR UPDATEOR DELETEON Emp_details

    FOR EACH ROW

    BEGIN

    IF INSERTING THEN

    INSERTINTO new_emps INSERT

    VALUES(:NEW.Emp_id,:NEW.name,:NEW.salary,

    :NEW.depart_id,:NEW.email,:NEW.job_id, SYSDATE);

    UPDATE depart UPDATE

    SET tot_depart_sal = tot_depart_sal+ :NEW.salary

    WHERE depart_id = :NEW. depart_id;

    ELSEIF DELETING THEN

    DELETEFROM EMP DELETE

    WHERE Emp_id = :OLD.Emp_id;

    UPDATE depart

    SET tot_depart_sal = tot_depart_sal- :OLD.salary

    WHERE depart_id = :OLD.depart_id

    ENDIF;

    END;

Previous article: Oracle Commands : Part 1
Next Article: Oracle Commands : Part 3