CODEWITHSUNDEEP
oracle-databaseselecttriggers
Some Java Guy
Some Java Guy

Reputation: 5118

Something is wrong in my oracle trigger

I need help in one of my Oracle Trigger syntax

CREATE OR REPLACE TRIGGER "TRG_TRIGGER" AFTER INSERT
ON T_TABLE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
INSERT INTO T_TABLE_TEST
  (DOC_ID , DOC_NAME)
VALUES
(:NEW.DOC_ID, SELECT DOC_NAME FROM OTHER_TABLE WHERE DOC_ID=DOC_ID);

Need DOC_NAME from other table. I think only my select query needs to be modified?

Upvotes: 0

Views: 92

Answers (3)

sagi
sagi

Reputation: 40481

You need to specify which doc_name to select, so I think you are missing a where clause.. try this:

INSERT INTO T_TABLE_TEST
(DOC_ID , DOC_NAME)
SELECT t.doc_id, t.DOC_NAME FROM OTHER_TABLE t where t.doc_id = :NEW.DOC_ID;

Of course I guessed that other table have column named doc_id, change it to the relation column.

Upvotes: 2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

The insert must be like this:

INSERT INTO T_TABLE_TEST
  (DOC_ID , DOC_NAME)
SELECT :NEW.DOC_ID, DOC_NAME FROM OTHER_TABLE where doc_id = :NEW.DOC_ID;

Just a note, you can skip REFERENCING NEW AS NEW OLD AS OLD since OLD and NEW are defaults.

Upvotes: 2

Boneist
Boneist

Reputation: 23578

As others have suggested, you could rewrite the insert...values statement to be an insert...as select statement, and that would make the most sense.

Just fyi, there are two problems with your original insert statement that leap out at me:

  1. You specified doc_id = doc_id in your select statement, whereas I think you meant doc_id = :new.doc_id. Your query would return all rows from the other table, and you'd get ORA-01427: single-row subquery returns more than one row, if your query was able to run.

  2. You didn't enclose the select statement within brackets. It should be:

--

INSERT INTO T_TABLE_TEST
 (DOC_ID , DOC_NAME)
VALUES 
 (:NEW.DOC_ID, (SELECT DOC_NAME FROM OTHER_TABLE WHERE DOC_ID=:NEW.DOC_ID));

Upvotes: 3

Related Questions