Backup mechanism using triggers - Steem Dev Community.

Hello Everyone,

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.

Before performing deleting event, backup table is empty.

Now we will delete the record from the main table then you can see that a message will show with subjected deleted id.

Now we check the backup and see that deleted record is successfully inserted into backup table.

Please cast witness vote to @bangla.Witness or set proxy to @rme.

_
Vote @bangla.witness as witness
Set Proxy @rme as proxy

Special Thanks.

Cc: @rme
Cc: @hungry-griffin
Cc: @pennsif

Thanks for giving your precious time to my post.
Follow | Upvote | Share | Comments
Follow me on Instagram.
Instagram

🆃🅷🅰️🅽🅺 🆈🅾️🆄