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).
Table1: Ord_mst
| Ord_no | Cust_cd | Status |
|---|---|---|
| 1 | C1 | P |
Table2: Ord_dtl
| Ord_no | Prod_cd | Qty |
|---|---|---|
| 1 | P1 | 100 |
| 1 | P2 | 200 |
Table3: Prod_mst
| Prod_cd | Prod_name | Qty_in_stock | Booked_qty |
|---|---|---|---|
| P1 | Floppies | 10000 | 1000 |
| P2 | Printers | 5000 | 600 |
| P3 | Modems | 3000 | 200 |
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 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:
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:
