Triggers in oracle

 -- Creating a table for demonstration purposes

CREATE TABLE your_table (

    id NUMBER PRIMARY KEY,

    data VARCHAR2(50),

    last_updated TIMESTAMP

);

-- BEFORE INSERT Trigger

CREATE OR REPLACE TRIGGER before_insert_trigger

BEFORE INSERT ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed before insert

    -- For example, setting a default value

    :NEW.last_updated := SYSTIMESTAMP;

END;

/

-- AFTER INSERT Trigger

CREATE OR REPLACE TRIGGER after_insert_trigger

AFTER INSERT ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed after insert

    -- For example, logging the insertion

    INSERT INTO log_table (log_message) VALUES ('New row inserted');

END;

/

-- BEFORE UPDATE Trigger

CREATE OR REPLACE TRIGGER before_update_trigger

BEFORE UPDATE ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed before update

    -- For example, preventing updates under certain conditions

    IF :NEW.data = 'restricted' THEN

        RAISE_APPLICATION_ERROR(-20001, 'Updates to "restricted" data are not allowed.');

    END IF;

END;

/

-- AFTER UPDATE Trigger

CREATE OR REPLACE TRIGGER after_update_trigger

AFTER UPDATE ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed after update

    -- For example, updating related records in another table

    UPDATE related_table

    SET related_data = :NEW.data

    WHERE related_id = :NEW.id;

END;

/

-- BEFORE DELETE Trigger

CREATE OR REPLACE TRIGGER before_delete_trigger

BEFORE DELETE ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed before delete

    -- For example, preventing deletion of specific records

    IF :OLD.data = 'protected' THEN

        RAISE_APPLICATION_ERROR(-20002, 'Deletion of "protected" records is not allowed.');

    END IF;

END;

/

-- AFTER DELETE Trigger

CREATE OR REPLACE TRIGGER after_delete_trigger

AFTER DELETE ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed after delete

    -- For example, logging the deletion

    INSERT INTO log_table (log_message) VALUES ('Row deleted');

END;

/