Oracle 'statement level' Trigger

1
votes

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);
2
oracletriggers
ur trigger is fine.. It should work.. Check this link for trigger syntaxes.. plsql-tutorial.com/plsql-triggers.htm - Teja
the thing is if in the 'values' I replace the column names EVENTID and EMPNUM as 1,'A'...it works. But it still inserts multiple records in the audit table. i.e as many number of records inserted into EMP table. - user547453

2 Answers

3
votes

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;
0
votes

Use this piece of code:

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;