CODEWITHSUNDEEP
sqloracle-databasestored-procedures
Halfpint
Halfpint

Reputation: 4077

Oracle DBMS INSERT Procedure not working

I'm working on what should be a simple functionality for my database.

When a user signs up to my application, a field password_changed is initialised to 0, upon doing this in my user trigger I want to fire the below method to send that new user a message telling them that they should update their password.

CREATE OR REPLACE TRIGGER trg_users
BEFORE INSERT OR UPDATE ON users FOR EACH ROW
BEGIN
    -- Get the new user id
    IF :NEW.user_id IS NULL THEN
        SELECT seq_user_id.nextval
        INTO :NEW.user_id
        FROM sys.dual;
    END IF;

   -- Alert a user that they need to change their password
   IF :NEW.pass_changed IS NULL THEN
      :NEW.pass_changed := 0;
      send_alert(:NEW.user_id, 'Thank-you for registering, please change your password for security reasons!');
   END IF;
END;

This first trigger simply initialises password set to 0 and then calls my send_alert() function, taking the :NEW.user_id which gets populated by my sequence.

send_alert() PROCEDURE:

-- Sends an alert to the user noting that they haven't changed their password
CREATE OR REPLACE PROCEDURE send_alert( 
   this_user users.user_id%TYPE, 
   this_message STRING 
)
AS
BEGIN
   INSERT INTO messages 
   VALUES('', this_user, getSystemId(), 'ALERT', this_message, '', '');
END send_alert;

When this code runs, I get the error integrity constraint (PRCSE.INBOX_MESSAGE_TO_FK) violated - parent key not found I understand what this means - however the value for the parent key should be populated via this_user, if I substitute that field for an existing value the procedure completes and INSERTS for the old user.

I can only think that for some reason the :NEW.user_id is not getting passed, but I initialise its value before calling my procedure.

EDIT 10/04/14 13:48 GMT: To clear any confusion, getSystem() returns the master admin id

-- Gets the ID of the SYSTEM user via its unique email
CREATE OR REPLACE FUNCTION getSystemId 
    RETURN NUMBER
IS
    system_user users.user_id%TYPE;
    pragma autonomous_transaction;
BEGIN 
    SELECT user_id
    INTO   system_user
    FROM   users
    WHERE  user_email = '[email protected]'
    AND    user_permissions = 'ADMIN';
RETURN system_user;
COMMIT;
END getSystemId;

Any help would be appreciated - Regards, Alex.

Upvotes: 0

Views: 128

Answers (1)

Dmitry Nikiforov
Dmitry Nikiforov

Reputation: 3038

This is because you try to insert value in BEFORE trigger (e.g. before parent row is inserted):

SQL> create table t(x int primary key);


SQL> create table t_c(x int references t(x));


SQL> create or replace trigger tr_i
  2  before insert on t
  3  for each row
  4  begin
  5    insert into t_c(x) values(:new.x);
  6  end;
  7  /

SQL> insert into t values(1);
insert into t values(1)
            *
error in line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C00330529) voilated - parent key not found
ORA-06512: in  "SCOTT.TR_I", line 2 
ORA-04088: error in trigger execution 'SCOTT.TR_I' 


SQL> create or replace trigger tr_i
  2  after insert on t
  3  for each row
  4  begin
  5    insert into t_c(x) values(:new.x);
  6  end;
  7  /

SQL> insert into t values(1);

1 row inserted.

SQL> select * from t;

     X                                                                          
------                                                                          
     1                                                                          

SQL> select * from t_c;

     X                                                                          
------                                                                          
     1

Upvotes: 2

Related Questions