Forum Stats

  • 3,752,481 Users
  • 2,250,508 Discussions
  • 7,867,849 Comments

Discussions

Use a trigger to mark row as deleted using a flag instead of actually deleting the row

M.Emmanuel
M.Emmanuel Member Posts: 304
edited Aug 17, 2016 2:47AM in SQL & PL/SQL

I would like to mark deleted rows as deleted using a flag instead of actually removing them from database.

I would like to use a trigger so I do not have to care about that in the application layer.

pastedImage_0.png

And

pastedImage_1.png

But what should I do to specify that the row should not be deleted?

I am using Oracle 11g XE

Tagged:
M.Emmanuel

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 41,321 Red Diamond
    edited Aug 15, 2016 4:19AM Accepted Answer

    An example of what Andrew and Bruno have mentioned...

    SQL> create table mytable(id number, del varchar2(1))
      2  /Table created.SQL> create view vmytable as select * from mytable  2  /View created.SQL> create or replace trigger trg_mytable instead of delete on vmytable
      2  for each row
      3  begin
      4    update mytable set del = 'Y' where id = :old.id;
      5  end;
      6  /Trigger created.SQL> insert into vmytable (id) values (1);1 row created.SQL> insert into vmytable (id) values (2);1 row created.SQL> insert into vmytable (id) values (3);1 row created.SQL> commit;Commit complete.SQL> delete from vmytable where id = 2;1 row deleted.SQL> commit;Commit complete.SQL> select * from vmytable
      2  /        ID D
    ---------- -
            1
            2 Y
            3

    So, your APEX application would query, insert, update and delete through the view and the INSTEAD OF trigger would capture the delete and turn it into an update on the base table.

    M.EmmanuelM.Emmanuel
«1

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Aug 14, 2016 8:29AM

    It sounds like you want an instead of trigger, it's probably a drop down for triggering time.

    It's all in the docs https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS99888

    M.EmmanuelM.Emmanuel
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Aug 14, 2016 8:36AM

    not issue DELETE, but instead issue UPDATE

    M.EmmanuelM.Emmanuel
  • Vysakh Suresh - 3035408
    Vysakh Suresh - 3035408 Member Posts: 1,240 Gold Badge
    edited Aug 14, 2016 12:00PM

    Dear @M.Emmanuel,

    If you really require to update the FLAG when you delete a record, the following can serve you..

    Imagine there is a table TEMP like this..

    IDDELETE_FLAG
    1-
    2-
    3-

    we create another table DUMP having the same structure as TEMP say..

    create table dump as select * from temp where 1=0;

    now we create a trigger for our purpose like..

    CREATE OR REPLACE TRIGGER DELETE_TEMP

      AFTER DELETE

      ON TEMP

      REFERENCING NEW AS NEW OLD AS OLD

      FOR EACH ROW

    BEGIN

      INSERT INTO DUMP VALUES (:OLD.ID, 'DELETED');

    END;

    and execute the delete statement as a PL SQL block say..

    BEGIN

        DELETE FROM DUMP;

        DELETE FROM TEMP WHERE ID=2;

        INSERT INTO TEMP SELECT * FROM DUMP;

    END;

    now when you select data from temp it will look as below..

    SELECT * FROM TEMP ORDER BY 1

    IDDELETE_FLAG
    1-
    2DELETED
    3-

    Thanks and Regards,

    Vysakh Suresh

    M.EmmanuelM.Emmanuel
  • BrunoVroman
    BrunoVroman Member Posts: 1,848 Silver Crown
    edited Aug 14, 2016 12:02PM

    Hello,

    you might create a view over the table (something like SELECT col1, col2, ... FROM tbl WHERE flag_deleted = 0) and use "INSTEAD OF" triggers.

    If over time you expect many rows to be deleted,  you might use partitioning  (extra license cost), or you might use the "INSTEAD OF" trigger to actually delete from original table but insert into an "historical" table...

    Best regards,

    Bruno Vroman.

    M.EmmanuelM.Emmanuel
  • M.Emmanuel
    M.Emmanuel Member Posts: 304
    edited Aug 14, 2016 4:03PM

    I have read all answers and now I guess that it is not common not to delete a row, or at least it should not be done with a DML delete trigger.

    I thought it was pretty common to leave historical record in the same table.

    I think I would delete record with an historical table.

    Thanks to all,

  • Unknown
    edited Aug 14, 2016 5:09PM
    I thought it was pretty common to leave historical record in the same table.

    It is. You use a STATUS (flag) column and change the status to indicate a row is no longer active.

    You revoke permission to DELETE from the table and use UPDATE instead.

    Oracle 12c has in-table archiving where the 'flag' column is provided by Oracle.

    See the Oracle docs

    https://docs.oracle.com/database/121/VLDBG/GUID-5A76B6CE-C96D-49EE-9A89-0A2CB993A933.htm#VLDBG14154

    Using In-Database Archiving

    In-Database Archiving enables you to archive rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression, but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting a session parameter.With In-Database Archiving you can store more data for a longer period of time within a single database, without compromising application performance. Archived data can be compressed to help improve backup performance, and updates to archived data can be deferred during application upgrades to improve the performance of upgrades.To manage In-Database Archiving for a table, you must enable ROW ARCHIVAL for the table and manipulate the ORA_ARCHIVE_STATE hidden column of the table. Optionally, you specify either ACTIVE or ALL for the ROW ARCHIVAL VISIBILITY session parameter.For example, you can use the SQL statements similar to those in Example 5-9 to hide or show rows in a table. The purpose is to display only active data in most situations, but to maintain all data in case it is needed in specific situations.

    Also see this tutorial

    Using In-Database Row Archiving

    M.EmmanuelM.Emmanuel
  • M.Emmanuel
    M.Emmanuel Member Posts: 304
    edited Aug 14, 2016 6:43PM

    Thanks rp0438, right now I am in 11g XE but it is good to hear about this feature.

    Additionally, as I am using Apex as front-end, delete and update are expected to be used for their respective standard uses. So doing update for deleting might be an option, as you can modify the standard behaviour of APEX, but I think that is flowing against the tool.

  • EdStevens
    EdStevens Member Posts: 28,407 Gold Crown
    edited Aug 14, 2016 9:44PM
    M.Emmanuel wrote:Thanks rp0438, right now I am in 11g XE but it is good to hear about this feature.Additionally, as I am using Apex as front-end, delete and update are expected to be used for their respective standard uses. So doing update for deleting might be an option, as you can modify theWhy  standard behaviour of APEX, but I think that is flowing against the tool.

    How would that be "flowing against the tool"?  Apex doesn't care. It's your application to code as you see fit.  If the design is such that you wish to flag a row as "deleted" instead of actually deleting it, then code your application to do so.

    M.EmmanuelM.Emmanuel
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Aug 14, 2016 9:53PM

    The first thing that must occur, you need to decide whether the row remains in the table or the row gets removed from the table.

    One of the two must occur.

    If the row remains then an UPDATE statement is issued.

    It the row gets removed then DELETE is issued.

    You can't "set a flag" in a row that does not exist.

    M.EmmanuelM.Emmanuel
  • BluShadow
    BluShadow Member, Moderator Posts: 41,321 Red Diamond
    edited Aug 15, 2016 4:19AM Accepted Answer

    An example of what Andrew and Bruno have mentioned...

    SQL> create table mytable(id number, del varchar2(1))
      2  /Table created.SQL> create view vmytable as select * from mytable  2  /View created.SQL> create or replace trigger trg_mytable instead of delete on vmytable
      2  for each row
      3  begin
      4    update mytable set del = 'Y' where id = :old.id;
      5  end;
      6  /Trigger created.SQL> insert into vmytable (id) values (1);1 row created.SQL> insert into vmytable (id) values (2);1 row created.SQL> insert into vmytable (id) values (3);1 row created.SQL> commit;Commit complete.SQL> delete from vmytable where id = 2;1 row deleted.SQL> commit;Commit complete.SQL> select * from vmytable
      2  /        ID D
    ---------- -
            1
            2 Y
            3

    So, your APEX application would query, insert, update and delete through the view and the INSTEAD OF trigger would capture the delete and turn it into an update on the base table.

    M.EmmanuelM.Emmanuel
This discussion has been closed.