CODEWITHSUNDEEP
databaseoracle-databaseplsqltriggers
Prasoon Shukla
Prasoon Shukla

Reputation: 51

How to rectify Oracle Trigger Creation Error

I am trying to create a trigger on table 'EMP' with following task:- When a record is inserted or updated or deleted, then old record should be store in 'Empbackup' table with type and date of operation.

fields of table EMP are (empno, emp_panno, ename, job, hire_date, salary, age, city, pincode, state).

with this trigger block i am getting 'Warning: Trigger created with compilation errors.'

create or replace trigger emp_trigger
after insert or delete or update on emp
for each row
enable 
begin 
if inserting then
    insert into emp_backup values (:new.empno, :new.emp_panno, :new.ename, new.job, 
    :new.hire_date, :new.salary, :new.age, :new.city, :new.pincode, :new.state, :insert, :sysdate)
elseif deleting then
    insert into emp_backup values(:old.empno, :old.emp_panno, :old.ename, :old.job, 
    :old.hire_date, :old.salary, :old.age, :old.city, :old.pincode, :old.state, 
    :delete, :sysdate);
elseif updating then
    insert into emp_backup values (:old.empno, :old.emp_panno, :old.ename, :old.job, 
    :old.hire_date, :old.salary, :old.age, :old.city, :old.pincode, :old.state, 
    :update, :sysdate);
end if
end;

NOTE:-UPDATED PART AFTER SUCCESSFULLY CREATING TRIGGER

EMP table desciption:- EMPNO NUMBER(8) EMP_PANNO NUMBER(10) ENAME VARCHAR2(20) JOB VARCHAR2(15) HIRE_DATE DATE SALARY NUMBER(10) AGE NUMBER(8) CITY VARCHAR2(25) PINCODE NUMBER(8) STATE VARCHAR2(15)

Insert statement after successfully creating trigger:-

     insert into emp values (027, 7896, 'sudhir', 'dev', 
     to_date('19/08/1999', 'dd/mm/yyyy'), 5600, 34, 'senaica', 
     223021, 'uttar pradesh');

error:-

ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at "SYSTEM.EMP_TRIGGER", line 3
ORA-04088: error during execution of trigger 'SYSTEM.EMP_TRIGGER'

Upvotes: 0

Views: 212

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

Quite a few errors.

  • you can't use e.g. :insert, :sysdate; should be 'insert', sysdate
  • statement must be terminated by a semi-colon
  • it is elsif, not elseif
  • new.job is missing a colon

How to find out what's wrong? Like this:

Warning: Trigger created with compilation errors.

SQL> show err
Errors for TRIGGER EMP_TRIGGER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/5      PL/SQL: SQL Statement ignored
3/81     PL/SQL: ORA-00984: column not allowed here
SQL>

Or, query USER_ERRORS.

Sample tables (don't mind dummy datatypes) (temp instead of emp; temp_backup instead of emp_backup as I don't want to drop my own emp table):

SQL> CREATE TABLE temp
  2  (
  3     empno       NUMBER,
  4     emp_panno   NUMBER,
  5     ename       NUMBER,
  6     job         NUMBER,
  7     hire_date   NUMBER,
  8     salary      NUMBER,
  9     age         NUMBER,
 10     city        NUMBER,
 11     pincode     NUMBER,
 12     state       NUMBER
 13  );

Table created.

SQL> CREATE TABLE temp_backup
  2  AS
  3     SELECT * FROM temp;

Table created.

SQL>
SQL> ALTER TABLE temp_backup
  2     ADD (what NUMBER, when DATE);

Table altered.

SQL>

Trigger, after errors have been fixed:

SQL> create or replace trigger emp_trigger
  2  after insert or delete or update on temp
  3  for each row
  4  enable
  5  begin
  6  if inserting then
  7      insert into temp_backup values (:new.empno, :new.emp_panno, :new.ename, :new.job,
  8      :new.hire_date, :new.salary, :new.age, :new.city, :new.pincode, :new.state, 'insert', sysdate);
  9  elsif deleting then
 10      insert into temp_backup values(:old.empno, :old.emp_panno, :old.ename, :old.job,
 11      :old.hire_date, :old.salary, :old.age, :old.city, :old.pincode, :old.state,
 12      'delete', sysdate);
 13  elsif updating then
 14      insert into temp_backup values (:old.empno, :old.emp_panno, :old.ename, :old.job,
 15      :old.hire_date, :old.salary, :old.age, :old.city, :old.pincode, :old.state,
 16      'update', sysdate);
 17  end if;
 18  end;
 19  /

Trigger created.

SQL>

Although that's not an error, I'd suggest you to always name all columns you use. Don't just

insert into temp_backup values ...

Specify all columns:

insert into temp_values (empno, emp_panno, ename, ...) values ...

It is a little bit more typing, but pays off in long term.

[EDIT] After you posted EMP table description: as I said, if you name all columns involved and pay attention about datatypes, it works.

Sample tables:

SQL> CREATE TABLE temp
  2  (
  3     EMPNO       NUMBER (8),
  4     EMP_PANNO   NUMBER (10),
  5     ENAME       VARCHAR2 (20),
  6     JOB         VARCHAR2 (15),
  7     HIRE_DATE   DATE,
  8     SALARY      NUMBER (10),
  9     AGE         NUMBER (8),
 10     CITY        VARCHAR2 (25),
 11     PINCODE     NUMBER (8),
 12     STATE       VARCHAR2 (15)
 13  );

Table created.

SQL> CREATE TABLE temp_backup
  2  AS
  3     SELECT *
  4       FROM temp
  5      WHERE 1 = 2;

Table created.

SQL> ALTER TABLE temp_backup
  2     ADD (what varchar2(10), when DATE);

Table altered.

Trigger:

SQL> create or replace trigger emp_trigger
  2      after insert or delete or update on temp
  3      for each row
  4      enable
  5      begin
  6      if inserting then
  7          insert into temp_backup (empno, emp_panno, ename, job,
  8          hire_date, salary, age, city, pincode, state, what, when) values (:new.empno, :new.emp_panno, :new.ename, :new.job,
  9          :new.hire_date, :new.salary, :new.age, :new.city, :new.pincode, :new.state, 'insert', sysdate);
 10      elsif deleting then
 11         insert into temp_backup (empno, emp_panno, ename, job,
 12          hire_date, salary, age, city, pincode, state, what, when)values(:old.empno, :old.emp_panno, :old.ename, :old.job,
 13         :old.hire_date, :old.salary, :old.age, :old.city, :old.pincode, :old.state,
 14         'delete', sysdate);
 15     elsif updating then
 16         insert into temp_backup (empno, emp_panno, ename, job,
 17          hire_date, salary, age, city, pincode, state, what, when)values (:old.empno, :old.emp_panno, :old.ename, :old.job,
 18         :old.hire_date, :old.salary, :old.age, :old.city, :old.pincode, :old.state,
 19         'update', sysdate);
 20     end if;
 21     end;
 22  /

Trigger created.

Testing:

SQL> INSERT INTO temp (empno,
  2                   emp_panno,
  3                   ename,
  4                   job,
  5                   hire_date,
  6                   salary,
  7                   age,
  8                   city,
  9                   pincode,
 10                   state)
 11       VALUES (027,
 12               7896,
 13               'sudhir',
 14               'dev',
 15               TO_DATE ('19/08/1999', 'dd/mm/yyyy'),
 16               5600,
 17               34,
 18               'senaica',
 19               223021,
 20               'uttar pradesh');

1 row created.

Result:

SQL> select * from temp;

     EMPNO  EMP_PANNO ENAME                JOB             HIRE_DATE      SALARY
---------- ---------- -------------------- --------------- ---------- ----------
       AGE CITY                         PINCODE STATE
---------- ------------------------- ---------- ---------------
        27       7896 sudhir               dev             19/08/1999       5600
        34 senaica                       223021 uttar pradesh


SQL> select * from temp_backup;

     EMPNO  EMP_PANNO ENAME                JOB             HIRE_DATE      SALARY
---------- ---------- -------------------- --------------- ---------- ----------
       AGE CITY                         PINCODE STATE           WHAT
---------- ------------------------- ---------- --------------- ----------
WHEN
----------
        27       7896 sudhir               dev             19/08/1999       5600
        34 senaica                       223021 uttar pradesh   insert
25/06/2021


SQL>

Upvotes: 2

Related Questions