Oracle 'statement level' Trigger

[dudanski]

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

The thing is if in the insert '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.

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);

 

[Dagon]

Running the three statements in the correct order (i.e. create tables first), I get "ORA-984: column not allowed here". This is because you are trying to reference TRANID and EMPNUM but not actually getting them from anywhere.

As you are using statement level triggers, you can't use :NEW.tranid etc. So you will have to select the tranids and empnums that you just inserted from EMP in some way. However, I'm not sure how you will determine which ones to use if you have just inserted ten rows. All 10 will presumably have different TRANIDs and possibly EMPNO, so which TRANID/EMPNO are you going to choose in your insert?

Also, bear in mind that when you say insert 10 rows, that would mean insert 10 rows from a single SQL statement, not insert 10 rows from 10 different SQL statements.

Also, you would need some mechanism of knowing which EMP rows you'd just inserted. You might be able to do this using something like a DATE or ID column. Another option would be to use row level triggers to store the values of the EMPNUMs that you'd inserted in a package level PL/SQL table variable. You could then read back the values from this table.