Before Trigger - PL/SQL Program

Trigger

  • Triggers are Stored in database and executed by Oracle engine whenever some event occurs.
  • When a trigger is fired, SQL statement inside the trigger's PL/SQL code block can also fire the same or some other trigger. This is called cascading triggers.
  • Triggers are written to execute in response events like DML Statements (DELETE, INSERT, or UPDATE), DDL statements (CREATE, ALTER, DELETE), and database operation (SERVERERROR, LOGON, LOGOFF, STARTUP or SHUTDOWN).
Q. Create the following 3 tables and insert sample data as given below:

Table1: Ord_mst

Ord_noCust_cdStatus
1C1P

Table2: Ord_dtl

Ord_noProd_cdQty
1P1100
1P2200

Table3: Prod_mst

Prod_cdProd_nameQty_in_stockBooked_qty
P1Floppies100001000
P2Printers5000600
P3Modems3000200

Before Insert Trigger

a) Write a PL/SQL block for before insert trigger on table 'Ord_dtl' in such a way that the column 'Booked_qty' from table  'Prod_mst' should be increased accordingly.

Create table:

Table1:

CREATE TABLE Ord_mst ( Ord_no number, Cust_cd varchar(2), Status varchar(1) );


Table2:

CREATE TABLE Ord_dtl ( Ord_no number, Prod_cd varchar(2), Qty number(3) );


Table3:

CREATE TABLE Prod_mst ( Prod_cd varchar(2), Prod_name varchar(20), Qty_in_stock number, Booked_qty number );


Create a Trigger:

CREATE OR REPLACE TRIGGER Ord_dtl_1
BEFORE
INSERT ON Ord_dtl
FOR EACH ROW BEGIN
UPDATE Prod_mst
SET Booked_qty=Booked_qty-:new.Qty
WHERE Prod_cd=:new.Prod_cd; dbms_output.put_line();
END;
/


In the above code if values inserted in table Ors_dtl the 'Booked_qty' column from 'Prod_mst' should be increased accordingly.
  
Output:

before insert trigger

Before Delete Trigger

b) Write a PL/SQL block for delete trigger on Ord_dtl. A record deleted from table 'Ord_dtl' and the column 'Booked_qty' from table 'Prod_mst' should be decreased accordingly.

Answer:

Create a trigger:

CREATE OR REPLACE TRIGGER ORD_DTL_2
BEFORE
DELETE ON ORD_DTL
FOR EACH ROW BEGIN
UPDATE Prod_mst
SET Booked_qty=Booked_qty-:old.Qty
WHERE Prod_cd=:old.Prod_cd; dbms_output.put_line('data deleted and updated in prod_mst table'); END;


In the above code, if a row should be deleted from table 'Ord_dtl' and the column 'Booked_qty' from table 'Prod_mst' should be decreased accordingly.

Output:

delete trigger

Before Update Trigger

C) Write a PL/SQL block for before Update of column 'Prod_cd', Qty trigger on 'Ord_dtl'. The column Prod_cd or Qty should be updated and the 'Booked_qty' in 'Prod_mst' should be increased or decreased accordingly.

Answer:

Create a trigger:

CREATE TRIGGER Prod_cd_3
BEFORE
UPDATE ON ord_dtl
FOR EACH ROW BEGIN
UPDATE prod_mst
SET booked_qty=booked_qty-:old.qty+:new.qty,prod_cd=:new.prod_cd
WHERE prod_cd=:old.prod_cd;
END;


Output:

before update
▲