Oracle SQL Commands: Part 2

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

 

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