Oracle SQL Trigger Error- OCI_SUCCESS_WITH_INFO: ORA-24344

0
votes

I am using a trigger called "users_id_trigger" to auto increment the users.user_id column.

But in Object Browser its status is showing invalid! enter image description here

I am working on a PHP project where i am getting this error while i run the query from a PHP script. here is the error message:

oci_execute(): OCI_SUCCESS_WITH_INFO: ORA-24344: success with compilation error

Here is my method to create the entire table and related SEQUENCE and TRIGGER to be able to have auto increment feature in the user_id column.

public function create_users_table()
{
    // creating users table
    oci_execute(oci_parse($this->conn, "CREATE TABLE users(
        user_id NUMBER(10) NOT NULL,
        user_name VARCHAR2(100) NOT NULL,
        user_password VARCHAR(100) NOT NULL,
        user_email VARCHAR(100) NOT NULL,
        user_location VARCHAR(100) NOT NULL,
        CONSTRAINT users_pk PRIMARY KEY (user_id)
    )"));
    // creating users sequence
    oci_execute(oci_parse($this->conn, "CREATE SEQUENCE users_seq
        MINVALUE 1 
        START WITH 1 
        INCREMENT BY 1 
        NOCACHE;
    "));
    // creating user sequence trigger
    echo oci_execute(oci_parse($this->conn, "CREATE OR REPLACE TRIGGER users_id_trigger
        BEFORE INSERT ON users 
        FOR EACH ROW 
        BEGIN 
            :new.user_id := users_seq.nextval;
        END;
    "));
}

What i am doing wrong? because the table and sequence is created successfully but not the trigger.

EDIT:

while inserting a new row i got this error:

insert into users values(1, 'hasan', '1234', '[email protected]', 'Dhaka, Bangladesh') * ERROR at line 1: ORA-04098: trigger 'FLIPLISTS.USERS_ID_TRIGGER' is invalid and failed re-validation

Oracle Version i am using: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production (Windows 10)

Compilation Error: ORA-04082: NEW or OLD references not allowed in table level triggers

2
phpsqloracleoracle-sqldeveloper
Yes, Windows 10 64 bit – rakibtg

2 Answers

0
votes

If oracle version that you are using is less than 11g then Modify your trigger as below

CREATE OR REPLACE TRIGGER users_id_trigger
    BEFORE INSERT ON users 
    FOR EACH ROW 
    BEGIN 
        select users_seq.nextval into :new.user_id from dual ;
    END;
0
votes

The issue is that you have newlines on windows,so remove them.

 echo oci_execute(oci_parse($this->conn, trim(preg_replace('/\s+/', ' ', "CREATE OR REPLACE TRIGGER users_id_trigger
        BEFORE INSERT ON users 
        FOR EACH ROW 
        BEGIN 
            :new.user_id := users_seq.nextval;
        END;
    "));));