Insert records to table2 when records are deleted in table1

Hi User,

Assume that EMP is the Table in the oracle database with the Definition,

CREATE TABLE EMP

(

EMPNO NUMBER(4),

ENAME VARCHAR2(17),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2)

)

Once Deleting the record from Table EMP we are inserting in to EMP1 consider for example,

The Trigger should be,

CREATE TRIGGER delete_emp

AFTER DELETE

ON EMP

FOR EACH ROW

BEGIN

DBMS_OUTPUT.PUT_LINE (:OLD.empno);

INSERT INTO EMP1 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

VALUES(:OLD.empno,:OLD.ENAME,:OLD.JOB,:OLD.MGR,:OLD.HIREDATE,:OLD.SAL,:OLD.COMM,:OLD.DEPTNO);

END;

The trigger will also fire when you are deleting the record from the Database Table connecting to Oracle database through command prompt sqlplus command.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Shankar M13y ago
Hi User,

Assume that EMP is the Table in the oracle database with the Definition,

CREATE TABLE EMP
(
EMPNO NUMBER(4),
ENAME VARCHAR2(17),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)

Once Deleting the record from Table EMP we are inserting in to EMP1 consider for example,

The Trigger should be,

CREATE TRIGGER delete_emp
AFTER DELETE
ON EMP
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE (:OLD.empno);

INSERT INTO EMP1 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES(:OLD.empno,:OLD.ENAME,:OLD.JOB,:OLD.MGR,:OLD.HIREDATE,:OLD.SAL,:OLD.COMM,:OLD.DEPTNO);

END;

The trigger will also fire when you are deleting the record from the Database Table connecting to Oracle database through command prompt sqlplus command.
0