1

I have a table that has a column that is determined by a sequence using the identity column thing, so after a record is inserted (or during, I'm not sure how it works behind the scenes, only that it does) it gets a number one higher than the previous record.

I'm writing a program where there is a condition that leads to a record having that number also be in another column. Is there a way to do this without just making the record and then modifying it immediately afterwards?

Thanks!

Edit: My code:

String insertion = "INSERT INTO DANIEL.UNIQUE_PHYSICIAN "
                    + "(SOURCE_ID, ROOT_ID, SOURCE_TABLE, CONFIDENCE_IS_SAME,"
                    + " IS_ROOT_PHYS, FIRST_NAME, LAST_NAME, POSTAL_CODE) VALUES "
                    + "(" + p.getPhysicianID() + ", " + Integer.toString(root_id) + ", STI.PHYSICIAN, 1, 0, " + p.getFirstName()
                    + ", " + p.getLastName() + ", " + p.getPostalCode() + ");";

and the table:

CREATE TABLE UNIQUE_PHYSICIAN 
(
  SOURCE_ID NUMBER 
, ROOT_ID NUMBER 
, SOURCE_TABLE VARCHAR2(20 BYTE) 
, CONFIDENCE_IS_SAME FLOAT(126) 
, IS_ROOT_PHYS CHAR(1 BYTE) NOT NULL 
, UNIQUE_ID NUMBER NOT NULL 
, FIRST_NAME VARCHAR2(20 BYTE) 
, LAST_NAME VARCHAR2(20 BYTE) 
, POSTAL_CODE VARCHAR2(20 BYTE) 
, CONSTRAINT UNIQUE_PHYSICIAN_PK PRIMARY KEY 
  (
    UNIQUE_ID 
  )
  USING INDEX 
  (
      CREATE UNIQUE INDEX UNIQUE_PHYSICIAN_PK ON UNIQUE_PHYSICIAN (UNIQUE_ID ASC) 
      LOGGING 
      TABLESPACE DATAL03 
      PCTFREE 10 
      INITRANS 2 
      STORAGE 
      ( 
        INITIAL 65536 
        MINEXTENTS 1 
        MAXEXTENTS UNLIMITED 
        BUFFER_POOL DEFAULT 
      ) 
      NOPARALLEL 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE DATAL03 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS UNLIMITED 
  BUFFER_POOL DEFAULT 
) 
NOCOMPRESS 
NOPARALLEL;

In this instance, I want to set ROOT_ID to be equal to the UNIQUE_ID assigned to that record.

7
  • Show us the code you have! Jun 12, 2015 at 15:47
  • So, you want ROOT_ID to initially be equal to UNIQUE_ID. Can it change later (to a different value)? Jun 12, 2015 at 16:43
  • it doesn't matter if it can change later or not. In the present implementation it doesn't matter but it might matter later. The reason I'm doing this is because this insert is only running in certain conditions, where I want ROOT_ID and UNIQUE_ID to be equal. There are conditions where that's not the case. Jun 12, 2015 at 16:48
  • So, you want some inserts to do this (root same as unique) but some other inserts to not do it? Jun 12, 2015 at 16:50
  • This insert that I am writing right there. I don't want it to be a script or trigger or something associated with the table, I want to do it exclusively in that insertion statement at the top. Edit: actually, whenever IS_ROOT_PHYS is 1, root should be set to unique. I suppose that could be a trigger? Jun 12, 2015 at 16:52

2 Answers 2

1

If you have a condition that specifies 1005 whether root_id has to get the same value as unique_id, you could use a TRIGGER. Something like this:

CREATE OR REPLACE TRIGGER root_unique_trg
    BEFORE INSERT
    ON UNIQUE_PHYSICIAN
    FOR EACH ROW
    WHEN (NEW.is_root_phys = 1)
BEGIN
    :NEW.root_id := :NEW.unique_id ;
END ;

Tested in SQLfiddle

3
  • Did you try it? I haven't tested and I fear it may not work as it is. Jun 12, 2015 at 17:18
  • No, it doesn't work, I'm getting syntax problems. I managed to get it to work, see my answer. Jun 12, 2015 at 17:18
  • Yes, I was testing in SQLfiddle. Edited with a working version. Jun 12, 2015 at 17:41
1

I managed to produce a working trigger based on ypercube's help.

create or replace TRIGGER ROOT_PHYSICIAN_TRG 
BEFORE INSERT ON UNIQUE_PHYSICIAN
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW
BEGIN
IF  INSERTING AND :NEW.is_root_phys = 1
THEN :NEW.root_id := :NEW.unique_id;
END IF;
END;
1
  • 1
    The referencing old as old new as new line is pointless. And there is no need to have the inserting check in the if statement in a before insert trigger. More of a concern, though, assuming the unique_id is being assigned in a trigger, which seems like the only way your insert would work, is that Oracle is free to execute your triggers in any order but this code will fail if the trigger that assigns unique_id hasn't fired yet. Almost certainly, you'd want to add this logic to the existing trigger rather than creating a new trigger. Or set up a trigger order explicitly. Jun 12, 2015 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.