CODEWITHSUNDEEP
oracle-databaseplsqltriggersoracle11g
Alp
Alp

Reputation: 624

How to write a trigger which splits a row and inserts it to another table in Oracle 11g

I am developing a Libray Management System using JSF 2.2. I have two tables called Book and Author in database. I have Editor attribute in Book table and Name, Surname attributes in Author table. When user enters a new Book with using JSF interface, a trigger should be triggered. It should take Editor attribute information and splits into two parts. They are name and surname. After splitting it should insert them to Author table. For example in Editor table there is this data 'Dan Brown' the trigger which I wanted should split this as 'Dan' and 'Brown' and inserts Dan to Names attribute, Brown to Surname attribute which are in Author table. I looked for this on the internet and have something. But I do not have any information about Triggers. For example below code is running only for 1. row. How can I write a loop in trigger or how can I control this data(Dan Brown) is already in Authors table. What I find is this:

SELECT SUBSTR((SELECT DISTINCT EDITOR FROM BOOK WHERE ISBN=1),0,INSTR
((SELECT DISTINCT EDITOR FROM BOOK WHERE ISBN=1) ,' ')-1) FROM DUAL;

SELECT SUBSTR((SELECT DISTINCT EDITOR FROM BOOK WHERE ISBN=1),-(LENGTH((SELECT DISTINCT EDITOR     
FROM BOOK WHERE ISBN=1))-INSTR((SELECT DISTINCT EDITOR FROM BOOK WHERE ISBN=1),' '))) FROM DUAL;

I got 'Dan' and 'Brown' with using this. And insertin is easy. If you help me about Trigger I will appretiate for this. If this question is so easy I am sorry.

Upvotes: 0

Views: 162

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50077

Try something like the following:

CREATE TRIGGER BOOK_AI
  AFTER INSERT ON BOOK
  FOR EACH ROW
BEGIN
  INSERT INTO AUTHOR (NAME, SURNAME)
    VALUES (TRIM(SUBSTR(:NEW.EDITOR, 0, INSTR(:NEW.EDITOR, ' ')-1)),
            TRIM(SUBSTR(:NEW.EDITOR, INSTR(:NEW.EDITOR, ' ')+1)));
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN  -- If row already exists in author table
    RETURN;                   -- just continue onwards.
END BOOK_AI;

Best of luck.

Upvotes: 1

Related Questions