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.
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)
if p_handshake = 'd' then
delete USER_GROUP WHERE HMIUSERGROUPID = p_groupId;
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
Can someone please help me with this issue?
Thanks in advance.