CODEWITHSUNDEEP
sqloracle-databaseplsqlddl
Steve Shi
Steve Shi

Reputation: 15

how to insert data using trigger

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

Upvotes: 1

Views: 461

Answers (2)

Xaphas
Xaphas

Reputation: 519

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!

Upvotes: 3

Arif Sher Khan
Arif Sher Khan

Reputation: 585

The error is because of the column name(user) you are using in the dept_shadow table. USER is predefined that's why it won't work as column name in table. Rename it to 'audit_user' or anything you want, which is not keyword. It will work seamlessly.

And for trigger

CREATE OR REPLACE TRIGGER dept_trigger
AFTER INSERT
   ON dept
   FOR EACH ROW

DECLARE
   v_username varchar2(10);

BEGIN
   -- Find username of person performing the INSERT into the table
   SELECT user INTO v_username
   FROM dual;

   -- Insert record into shadow table
   INSERT INTO dept_shadow
   ( DEPTNO,
     DNAME,
     LOC,
     AUDIT_USER,
     MODTIME )
   VALUES
   ( :new.DEPTNO,
     :new.DNAME,
     :new.LOC,
     v_username, 
     :new.MODTIME
   );
END;
/

Hope this will work for you.

Upvotes: 0

Related Questions