CODEWITHSUNDEEP
sqloracle-databaseplsqldatabase-trigger
A. Rahman
A. Rahman

Reputation: 71

Trigger compiles without errors, But it does not add data

I'm trying to update the table called 'saturn.szrunsp' after a update or insert happens on the table 'saturn.szraspr'. After kind suggestions i've created a trigger without any errors. however, the main part still doesnt seem to work which is the trigger to add data onto the table saturn.szrunsp after update on table saturn.szraspr. please see all codes of table and trigger below. UPDATED!! 

-- INSERTING table --

        CREATE TABLE SATURN.SZRUNSP
    (
      SZRUNSP_STUDENT_NO       VARCHAR2(9 CHAR)     NOT NULL,
      SZRUNSP_PIDM             NUMBER(8)            NOT NULL,
      SZRUNSP_STUDENT_NAME     VARCHAR2(60 CHAR)    NOT NULL,
      SZRUNSP_SUSPEND_ACCOUNT  VARCHAR2(1 CHAR)     NOT NULL,
      SZRUNSP_UNSUSPEND_DATE   DATE                 NOT NULL,
      SZRUNSP_ACTIVITY_DATE    DATE                 NOT NULL,
      SZRUNSP_USER_ID          VARCHAR2(30 CHAR),
      SZRUNSP_ACTION           VARCHAR2(30 CHAR),
      SZRUNSP_SURROGATE_ID     NUMBER(19)           NOT NULL,
      SZRUNSP_VERSION          NUMBER(19)           NOT NULL,
      SZRUNSP_VPDI_CODE        VARCHAR2(6 CHAR)
    )


-- TRIGGER TABLE -- 

    CREATE TABLE SATURN.SZRASPR
(
  SZRASPR_STUDENT_NO             VARCHAR2(9 CHAR) NOT NULL,
  SZRASPR_STUDENT_NAME           VARCHAR2(60 CHAR) NOT NULL,
  SZRASPR_DATE_OF_BIRTH          VARCHAR2(8 CHAR) NOT NULL,
  SZRASPR_SUSPEND_ACCOUNT        VARCHAR2(1 CHAR) NOT NULL,
  SZRASPR_EMAIL_ADDRESS          VARCHAR2(100 CHAR) NOT NULL,
  SZRASPR_STUDENT_YEAR           VARCHAR2(2 CHAR) NOT NULL,
  SZRASPR_FACULTY                VARCHAR2(60 CHAR) NOT NULL,
  SZRASPR_SCHOOL_OF_STUDY        VARCHAR2(60 CHAR) NOT NULL,
  SZRASPR_DEGREE_NAME            VARCHAR2(100 CHAR) NOT NULL,
  SZRASPR_MODE_OF_STUDY          VARCHAR2(20 CHAR) NOT NULL,
  SZRASPR_FEE_STATUS             VARCHAR2(20 CHAR) NOT NULL,
  SZRASPR_YEAR_START             VARCHAR2(4 CHAR) NOT NULL,
  SZRASPR_SEMESTER_START         VARCHAR2(5 CHAR) NOT NULL,
  SZRASPR_ENROLMENT_STATUS       VARCHAR2(2 CHAR) NOT NULL,
  SZRASPR_LEAVING_DATE           VARCHAR2(8 CHAR),
  SZRASPR_PENDING_ACTIVITY_DATE  DATE,
  SZRASPR_ACTIVE_ACTIVITY_DATE   DATE,
  SZRASPR_PENDING_STATUS         VARCHAR2(2 CHAR),
  SZRASPR_ACTIVE_STATUS          VARCHAR2(2 CHAR),
  SZRASPR_PIDM                   NUMBER(8)      NOT NULL,
  SZRASPR_TERM_CODE              VARCHAR2(6 CHAR) NOT NULL,
  SZRASPR_ASPIRE_ERROR_REPORTED  VARCHAR2(10 CHAR),
  SZRASPR_ERROR_TEXT             VARCHAR2(100 CHAR),
  SZRASPR_SEQ_NO                 NUMBER,
  SZRASPR_SEND                   INTEGER,
  SZRASPR_SURROGATE_ID           NUMBER(19),
  SZRASPR_VERSION                NUMBER(19),
  SZRASPR_USER_ID                VARCHAR2(30 CHAR),
  SZRASPR_DATA_ORIGIN            VARCHAR2(30 CHAR),
  SZRASPR_ACTIVITY_DATE          DATE,
  SZRASPR_VPDI_CODE              VARCHAR2(6 CHAR)
);



--Trigger--

    CREATE OR REPLACE TRIGGER szrunsp_suspend_trigger
after update or insert on SATURN.SZRASPR
for each row     
declare
    SZRUNSP_USER_ID varchar2(30 CHAR);
begin
    select user into SZRUNSP_USER_ID from dual;

    insert into SATURN.SZRUNSP values
        (:new.SZRASPR_STUDENT_NO,
         :new.SZRASPR_PIDM,
         :new.SZRASPR_STUDENT_NAME,
         :new.SZRASPR_SUSPEND_ACCOUNT,
         SYSDATE,
         SYSDATE,
         user,
         :new.SZRASPR_SURROGATE_ID,
         :new.SZRASPR_VERSION,
         :new.SZRASPR_VPDI_CODE);
 end;
/

Upvotes: 3

Views: 98

Answers (2)

APC
APC

Reputation: 146239

Not sure why you get this error:

[Error] ORA-00947 (9: 20): PL/SQL: ORA-00947: not enough values

The number of values in the INSERT statement matches the table you posted. So, if you are getting that error you need to correct your question.

[Error] PLS-00049 (10: 2): PLS-00049: bad bind variable 'NEW.SZRUNSP_STUDENT_NO'

All the columns referenced in the INSERT statement have the same names as the target table, saturn.szrunsp. However the bind names must match the columns of the triggering table. saturn.szraspr. You say

saturn.szraspr has szraspr_student_no column

so you need to edit the trigger to match:

CREATE OR REPLACE TRIGGER szrunsp_suspend_trigger
after update or insert on szraspr
for each row     
declare
    szrunsp_user_id varchar2(30);
begin
    select user into szrunsp_user_id from dual;

    insert into szrunsp values
        (:new.SZRASPR_STUDENT_NO,
         :new.SZRASPR_PIDM,
         :new.SZRASPR_STUDENT_NAME,
         :new.SZRASPR_SUSPEND_ACCOUNT,
         SYSDATE,
         SYSDATE,
         user,
         :new.SZRASPR_ACTION,
         '',
         '',
         '');
 end;
/

In Oracle an empty string is null so you will get NOT NULL violations on SZRUNSP_SURROGATE_ID and SZRUNSP_VERSION which you need to handle.

Upvotes: 1

Christian Palmer
Christian Palmer

Reputation: 1302

You are inserting NULL values into 

  SZRUNSP_SURROGATE_ID     NUMBER(19)           NOT NULL,
  SZRUNSP_VERSION          NUMBER(19)           NOT NULL,

And you can only use the :new prefix for columns on the triggering table - not on the table you're inserting into

Upvotes: 1

Related Questions