#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    6
    Rep Power
    0

    Delete record in an update trigger


    English is not my native language, so pardon me for my bad english. Neither am i an expert on Oracle databases, the following case is succesfully done with mssql databases.

    Case:
    Table UserGroup
    Columns id, name, handshake

    When the handshake is set to 'd', this record should be deleted. I know it is bad behaviour by design.

    What have I done so far:
    - created an after update trigger (mutual error)
    Caused by trying a delete action in the update action, not possible.
    - created a view in combination of instead of update trigger.
    This causes also mutual error, or if ignored (PRAGMA AUTONOMOUS_TRANSACTION), an deadlock.

    Code so far:

    create or replace procedure Delete_UserGroup_sp(p_groupId in USER_GROUP.HMIUSERGROUPID%TYPE, p_handshake in USER_GROUP.HANDSHAKE%TYPE)
    is
    begin
    if p_handshake = 'd' then
    delete USER_GROUP WHERE HMIUSERGROUPID = p_groupId;
    commit;
    end if;
    end;

    create or replace view USERGROUP_V as select * from USER_GROUP

    create or replace trigger USER_GROUP_T1
    instead of update on USERGROUP_V
    for each row
    declare
    PRAGMA AUTONOMOUS_TRANSACTION;
    begin
    Delete_UserGroup_sp(:new.HMIUSERGROUPID, :new.HANDSHAKE);
    end;

    Can someone please help me with this issue?
    Thanks in advance.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    The only workaround for this really bad design is probably to create a statement level trigger instead of a row level trigger (the usual pattern to avoid the "table is mutating" error). Which is more or less what MS SQL Server is doing (there is no "row level trigger in SQL Server).

    You would wind up with a row level trigger to collect all updated rows in e.g. a global temporary table (note that temp tables in Oracle are completely different than in SQL Server).

    Then in the statement level trigger you should be able to delete the rows that were collected in the temp table.

    But the real (and most efficient) solution to this problem is to use DELETE to remove the rows from the table.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by shammat
    The only workaround for this really bad design is probably to create a statement level trigger instead of a row level trigger (the usual pattern to avoid the "table is mutating" error). Which is more or less what MS SQL Server is doing (there is no "row level trigger in SQL Server).

    You would wind up with a row level trigger to collect all updated rows in e.g. a global temporary table (note that temp tables in Oracle are completely different than in SQL Server).

    Then in the statement level trigger you should be able to delete the rows that were collected in the temp table.

    But the real (and most efficient) solution to this problem is to use DELETE to remove the rows from the table.
    I will check the statement level trigger by using a temp table.
    And yes, it is a poor design, and no, not designed by me.

    If it all doesn't work, i'll probably create a procedure which will be called once in a while. This procedure will call the DELETE in order to delete the usergroups with the handshake on 'd'.

    edit: Thanks for the quick reply
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    6
    Rep Power
    0
    And here it is....

    create or replace procedure Delete_UserGroup_sp
    is
    begin
    delete USER_GROUP WHERE HANDSHAKE = 'd';
    end;
    /

    create or replace trigger Delete_UserGroup_tr
    after update on USER_GROUP

    begin
    Delete_UserGroup_sp;
    end;
    /

    Damn, got stucked for too long with this.
    Thanks shammat for pointing me to the right direction.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by Jabbo
    Damn, got stucked for too long with this.
    Thanks shammat for pointing me to the right direction.
    You can make the trigger a bit more efficient by only letting it happen when the handshake column is updated. No need to run the delete procedure when (only) other columns change

    Code:
    create or replace trigger Delete_UserGroup_tr
    after update of handshake on USER_GROUP
    begin
    Delete_UserGroup_sp;
    end;
    /
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by shammat
    You can make the trigger a bit more efficient by only letting it happen when the handshake column is updated. No need to run the delete procedure when (only) other columns change

    Code:
    create or replace trigger Delete_UserGroup_tr
    after update of handshake on USER_GROUP
    begin
    Delete_UserGroup_sp;
    end;
    /
    Nice catch, thanks.

IMN logo majestic logo threadwatch logo seochat tools logo