Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

Need to copy deleted row into Table-2 from Table-1 when a row is deleted from Table-1

HarikrishnanSep 16 2022

Hi All,
Scenario: We have two tables Table-1 & Table-2.
Table-1 has login time(row) of current login users, when user logout the particular row get deleted.
when user logout(when a row get deleted), we need to copy that row to Table-2 with an extra field -sysdate, so that we have their logout time as well.
I have tried using triggers..
I partially managed to achieve this requirement. Only when a single user is login to the system this trigger is working fine. but when multiple users are logged in trigger is not working.
may be it's because a where condition is not written in trigger & I don't know how can i include a where condition here.
Please help me to resolve this issue & is there any other approach available.
Thanks in advance .
I'm providing the trigger code below.(also add as an attachment)

Login- Logoff Trigger.txt (1.1 KB)
CREATE OR REPLACE TRIGGER LOGIN_LOGOFF_HISTORY_TRIG
AFTER DELETE ON FND_CLIENT_LOGON_TAB
FOR EACH ROW
DECLARE
V_SESSION_ID VARCHAR2(100) DEFAULT NULL;
V_NODE VARCHAR2(200) DEFAULT NULL;
V_DIRECTORY_ID VARCHAR2(2000) DEFAULT NULL;
V_OS_USER VARCHAR2(200) DEFAULT NULL;
V_MACHINE VARCHAR2(200) DEFAULT NULL;
V_PROGRAM VARCHAR2(2000) DEFAULT NULL;
V_USER_AGENT VARCHAR2(2000) DEFAULT NULL;
V_SESSION_CREATED DATE DEFAULT NULL;
V_ROWVERSION DATE DEFAULT NULL;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT SESSION_ID,DIRECTORY_ID,OS_USER,MACHINE,PROGRAM,USER_AGENT,SESSION_CREATED,ROWVERSION
INTO V_SESSION_ID,V_DIRECTORY_ID,V_OS_USER,V_MACHINE,V_PROGRAM,V_USER_AGENT,V_SESSION_CREATED,V_ROWVERSION
FROM FND_CLIENT_LOGON_TAB;

INSERT INTO LOGIN_LOGOFF_HISTORY_TAB
(SESSION_ID,DIRECTORY_ID,OS_USER,MACHINE,PROGRAM,USER_AGENT,SESSION_CREATED,ROWVERSION,ACTIVITY,ACTIVITY_SYSDATE)
VALUES
(V_SESSION_ID,V_DIRECTORY_ID,V_OS_USER,V_MACHINE,V_PROGRAM,V_USER_AGENT,V_SESSION_CREATED,V_ROWVERSION,'LOGOFF',SYSDATE);
COMMIT;
END;

Best Regards,
Hari

This post has been answered by Harikrishnan on Sep 19 2022
Jump to Answer
Comments
Post Details
Added on Sep 16 2022
3 comments
62 views