New to Nutbox?

Backup mechanism using triggers - Steem Dev Community.

1 comment

21 days agoSteemit3 min read

Hello Everyone,

Yellow White Bold Best AI Websites YouTube Thumbnail.png

We already discussed about trigger and its syntax now we look into it deeply, Triggers allow us to automate actions based on changes to our data. In this blog post, we will explore how to use triggers to create a backup mechanism for a table in database. we will try to know it practically so let's start.

we have a table named a_test containing information of employees. To safeguard this data against accidental deletions, we want to implement a backup mechanism. We will create a backup table and use a trigger to automatically store deleted entries from a_test into this backup table.

Tables Creation.

CREATE TABLE a_test (
    EMPLOYEE_ID   NUMBER,
    EMPLOYEE_NAME VARCHAR2(100),
    SALARY        NUMBER,
    PROMOTION_DATE DATE
);

CREATE TABLE backup (
    EMPLOYEE_ID   NUMBER,
    EMPLOYEE_NAME VARCHAR2(100),
    SALARY        NUMBER,
    PROMOTION_DATE DATE
);

Now, we will create a trigger named 'SA' on the a_test table that will run before a deletion event occurs. This trigger will capture the deleted entry and insert it into our backup table. Additionally, it will display a message indicating which entity was deleted.

CREATE OR REPLACE TRIGGER sa
BEFORE DELETE ON a_test
FOR EACH ROW
DECLARE
    ENTITY VARCHAR2(20);
BEGIN
    INSERT INTO backup VALUES (:old.EMPLOYEE_ID, :old.EMPLOYEE_NAME, :old.SALARY, :old.PROMOTION_DATE);
    
    ENTITY := :old.EMPLOYEE_ID;
    DBMS_OUTPUT.PUT_LINE('Deleted entity: ' || ENTITY || ' has been removed from a_test.');
END;

So, by using triggers we can enhance the reliability and security of our database systems.

So, check it by practically, first of all i will run both tables and check then data. In the below screenshot, you can see the 1 row in main table name a_test.