0

How can I write a sequence and trigger that upon insert into a table will check if the ID attribute (and INTEGER) is null.

If null it should increment through from 1 upwards checking if the value itself if already a primary key and if not that should use that as the primary key to insert.

Otherwise if id is not null it should update the row at the id given

I have this so far but am not sure about it

CREATE SEQUENCE create_student_id
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER new_student_id BEFORE
INSERT ON orders
    IF StudentID == null
    THEN
        FOR EACH ROW BEGIN
            :new.StudentID := create_student_id.nextval;
    END IF
--Need to update the table otherwise
    END;
Joe Moore
  • 15
  • 3
  • Is this Oracle? It really matters in terms of syntax. And, if the INSERT on Orders happens to have a primary key already in use, you want it to UPDATE the existing row instead? I just want to confirm your desired behavior. – CoffeeNeedCoffee Nov 24 '20 at 19:16

1 Answers1

0

Are you trying to implement a upsert method? Doing a index validation inside a trigger seems to me like a replacing the inner working of the DB. But for curiosity purposes, the following snippet shows the idea, but won't work in concurrency situation.

CREATE OR REPLACE TRIGGER new_student_id 
  BEFORE INSERT ON orders
FOR EACH ROW BEGIN
  DECLARE count 1 INT;
  IF StudentID == null THEN        
    WHILE (count != 0) LOOP 
      :new.StudentID := create_student_id.nextval;
      SELECT COUNT(1) INTO COUNT FROM orders WHERE StudentID = :new.StudentID
    END LOOP;
    -- If we got to this place the StudentID is now an usable key index 
  END IF;

--Need to update the table otherwise
END;
PandaCheLion
  • 446
  • 5
  • 15