CODEWITHSUNDEEP
sqloracle-databaseplsqldatabase-trigger
Matt
Matt

Reputation: 77

Trigger compiles but doesn't insert rows

I'm trying to create a TRIGGER that when fired inserts the data into a another table with USER and SYSDATE data. The script but no data is inserted from the TRIGGER.

I'm running Oracle Server. 

 SET SERVEROUTPUT ON;

  CREATE TABLE dept1
 ( DEPTNO NUMBER(3) PRIMARY KEY,
  DNAME VARCHAR2(20),
  LOC VARCHAR2(20)
 );

 CREATE TABLE dept1_shadow
 ( DEPTNO NUMBER(3) PRIMARY KEY,
  DNAME VARCHAR2(20),
  LOC VARCHAR2(20),
  USER_ VARCHAR2(32),
  MODTIME CHAR(17)
  );

 INSERT INTO dept1 VALUES (10, 'ACCOUNTING', 'NEW YORK');    
 INSERT INTO dept1 VALUES (20, 'RESEARCH',   'DALLAS');    
 INSERT INTO dept1 VALUES (30, 'SALES',      'CHICAGO');   
 INSERT INTO dept1  VALUES (40, 'OPERATIONS', 'WASHINGTON (D.C.)');    
 INSERT INTO dept1  VALUES (50, 'MARKETING', 'BOSTON');


CREATE OR REPLACE TRIGGER row_dept1_trigger
 AFTER INSERT ON dept1

 FOR EACH ROW

 DECLARE

 MODTIME CHAR(17);
 USER_ VARCHAR2(32);

 BEGIN

 MODTIME := TO_CHAR(SYSDATE);
 USER_ := User;

 IF INSERTING THEN
 INSERT INTO dept1_shadow (deptno, dname,loc, user_, modtime)
 VALUES (:new.deptno, :new.dname,:new.loc,user_, modtime);
 END IF;

 END;
 /

I'm not getting any errors.

Upvotes: 2

Views: 66

Answers (2)

Bohemian
Bohemian

Reputation: 424973

Move the insert statements after creating the trigger.

Upvotes: 6

Belayer
Belayer

Reputation: 14861

Possibility, there was no commit. If you ran in the order of the layout the inserts weer done before the trigger existed. Possibility, there was no commit.
A couple suggestions:

  1. Change the definition of modtime to DATE. Storing a date as a string eventually causes problems somewhere down the line.
  2. Remove both local variables and just use USER and SYSDATE in the values clause.
  3. Finally remove the IF INSERTING statement. You have defined a AFTER INSERT trigger, so that if statement will always be true.

So result:

create or replace trigger row_dept1_trigger
   after insert on dept1
   for each row
 begin
    insert into dept1_shadow (deptno, dname,loc, user_, modtime)
         values (:new.deptno, :new.dname,:new.loc,user, sysdate);
 end;

Upvotes: 2

Related Questions