Oracle ‘statement level’ Trigger

oracletriggers

I want to create a Statement level Trigger which means I want to insert only one record into table EMP_AUDIT when 1 or more rows are inserted into table EMP. For example: if I have 10 records inserted into EMP, then only 1 record should be inserted into EMP_AUDIT table.

There are no constraints on columns. (i.e. can be NULL)

I tried to use the following trigger but it gives me the Error(2,2): PL/SQL: SQL Statement ignored
Error(2,14): PL/SQL: ORA-00947: not enough values

   CREATE OR REPLACE
    TRIGGER TRIG_EMP AFTER INSERT ON EMP
   BEGIN
    INSERT INTO EMP_AUDIT
    VALUES (TRANID,EMPNUM,SYSDATE); 
   END;

   CREATE TABLE EMP
   (TRANID NUMBER,
    EMPNUM VARCHAR2(100),
    EMPLOC VARCHAR2(100)); 

   CREATE TABLE EMP_AUDIT
   (EVENTID NUMBER,
    EMPNUM VARCHAR2(100),
    ENTRDATE DATE);

Best Answer

The statement-level trigger (which you have) cannot see the data that was inserted. After all, if there were 10 rows inserted, what values should the columns be for your audit table?

You need a row-level trigger for this to work, e.g.:

CREATE OR REPLACE
TRIGGER TRIG_EMP
  AFTER INSERT ON EMP
  FOR EACH ROW
BEGIN
  INSERT INTO EMP_AUDIT
  VALUES (:NEW.TRANID,:NEW.EMPNUM,:NEW.SYSDATE); 
END;
Related Topic