DBMS-LAB-IV-SEM

Exercise – 13

Develop Programs using BEFORE and AFTER Triggers, Row and Statement Triggers and INSTEAD OF Triggers

-- Creating a hypothetical table
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    order_date DATE,
    total_amount NUMBER
);

-- Creating a sequence to generate order IDs
CREATE SEQUENCE order_id_seq START WITH 1 INCREMENT BY 1;

-- Creating a trigger to populate order ID before insert
CREATE OR REPLACE TRIGGER before_insert_order
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    SELECT order_id_seq.NEXTVAL INTO :NEW.order_id FROM DUAL;
END;
/

-- Creating a trigger to update total amount after insert
CREATE OR REPLACE TRIGGER after_insert_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE orders
    SET total_amount = :NEW.total_amount * 1.1 -- Applying 10% tax
    WHERE order_id = :NEW.order_id;
END;
/

-- Creating a trigger to log inserted orders
CREATE OR REPLACE TRIGGER log_inserted_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_log (order_id, order_date, total_amount)
    VALUES (:NEW.order_id, :NEW.order_date, :NEW.total_amount);
END;
/

-- Creating a hypothetical view for orders with tax included
CREATE OR REPLACE VIEW orders_with_tax AS
SELECT order_id, order_date, total_amount AS total_amount_without_tax, 
       total_amount * 1.1 AS total_amount_with_tax
FROM orders;

-- Creating an INSTEAD OF trigger for the view to handle updates
CREATE OR REPLACE TRIGGER instead_of_update_orders_with_tax
INSTEAD OF UPDATE ON orders_with_tax
FOR EACH ROW
BEGIN
    UPDATE orders
    SET total_amount = :NEW.total_amount_without_tax / 1.1
    WHERE order_id = :OLD.order_id;
END;
/

Explanation: