how to insert data using trigger

Question

I got question about using trigger to insert data, for instance, I do have two tables, and second table has attributes and records with table, except additional two attributes, like below:

CREATE TABLE dept
( 
    DEPTNO NUMBER(3) PRIMARY KEY,
    DNAME VARCHAR2(16),
    LOC VARCHAR2(16) 
);

CREATE TABLE dept_shadow
( 
    DEPTNO  NUMBER(3) PRIMARY KEY,
    DNAME   VARCHAR2(16),
    LOC     VARCHAR2(16),
    USER    VARCHAR2(32),
    MODTIME CHAR(17)
);

and I want create a trigger to track all inserts into a table.

surprisedly, I got error about creating table:

Error starting at line : 11 in command -
CREATE TABLE dept_shadow
( 
    DEPTNO  NUMBER(3) PRIMARY KEY,
    DNAME   VARCHAR2(16),
    LOC     VARCHAR2(16),
    USER    VARCHAR2(32),
    MODTIME CHAR(17)
)
Error report -
ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

I have no idea about this error and does anyone can tell me how to do this job by create trigger? Since there is no actual records to insert! Any suggestions are appreciated

Answer 1

Okay, so the error you are getting is because oracle (like all databases) has some reserved words. Now I'm not 100% sure because I don't tend to work with Oracle DB all that often, but I would assume that you cannot use the word USER for that reason. Try using USERNAME or USERDESCRIPTION or something like that instead.

Now for the trigger:

CREATE OR REPLACE TRIGGER trg_shadow
   BEFORE INSERT OR UPDATE OR DELETE
   ON dept_shadow
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
   MODTIME   char (17);
BEGIN
   IF INSERTING
   THEN
      -- do something
   ELSIF UPDATING
   THEN
      -- do something
   ELSIF DELETING
   THEN
      -- do something
   END IF;

From there, you can access "new" data through :NEW and "old" data through :OLD.

EDIT:

The difference of a BEFORE and AFTER trigger is when they are executed and both have a valid use.

BEFORE triggers may be used to validate data BEFORE inserting or updating. So for instance if you don't want to update the rows that would otherwise have the value 0 in column x.

AFTER triggers may be used to validate the new data AFTER inserting. So for instance if you want to delete all rows that now have the value 0 in column x.

It doesn't really matter in your case though.

Hope that helps!