Database Triggers

[Phindile]

Hi Erman
I have two schemas/users. HR_USER owns a table called DUMMY_PERSON and HRPERSAL_USER owns a table called DUMMY_PER_GEN_DET.

I'm trying to create a trigger that will update table DUMMY_PERSON whenever there's a new insert in a column called PERSALNO on table DUMMY_PER_GEN_DET.

I'm getting an error which says table or view doesn't exist, but I granted HRPERSAL_USER update and insert privileges on table DUMMY_PERSON.

This is my code:
create or replace TRIGGER DUMMY_HRPERSAL_TRIGGER AFTER INSERT OR UPDATE ON DUMMY_PER_GEN_DET FOR EACH ROW WHEN (new.R_INDICATOR = '0')
BEGIN INSERT INTO DUMMY_PERSON (PERSALNO,COL_SEQ) VALUES (:new.PERSALNO,DUMMY_HRPERSAL_SEQ.NEXTVAL); END;

Is there something which maybe I didn't include in my code?

[ErmanArslansOracleBlog]

1)Who is the owner of the trigger?
2)what happens when you create trigger with the following ->

create or replace TRIGGER DUMMY_HRPERSAL_TRIGGER AFTER INSERT OR UPDATE ON DUMMY_PER_GEN_DET FOR EACH ROW WHEN (new.R_INDICATOR = '0')
BEGIN INSERT INTO HR_USER.DUMMY_PERSON (PERSALNO,COL_SEQ) VALUES (:new.PERSALNO,DUMMY_HRPERSAL_SEQ.NEXTVAL); END;

(change - HR_USER.DUMMY_PERSON)

[Phindile]

Thanks for your response,

1) HRPERSAL_USER is the owner of the trigger

2) I actually tried that, and the trigger does compile but with errors. When I do an insert on DUMMY_PER_GEN_DET, it doesn't update on DUMMY_PERSON.

These are the errors I'm getting on the logging page - log

SEVERE	701	5056220	org.openide.util.RequestProcessor$Processor	Error in RequestProcessor oracle.ideimpl.usages.UsagesTrackerImpl$2
SEVERE	127	0	org.openide.util.RequestProcessor$Processor	Error in RequestProcessor oracle.ideimpl.usages.UsagesTrackerImpl$3

 

On Tue, Nov 7, 2017 at 10:27 AM, ErmanArslansOracleBlog [via Erman Arslan's Oracle Forum] <[hidden email]> wrote:

1)Who is the owner of the trigger?
2)what happens when you create trigger with the following ->

create or replace TRIGGER DUMMY_HRPERSAL_TRIGGER AFTER INSERT OR UPDATE ON DUMMY_PER_GEN_DET FOR EACH ROW WHEN (new.R_INDICATOR = '0')
BEGIN INSERT INTO HR_USER.DUMMY_PERSON (PERSALNO,COL_SEQ) VALUES (:new.PERSALNO,DUMMY_HRPERSAL_SEQ.NEXTVAL); END;

(change - HR_USER.DUMMY_PERSON)

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/Database-Triggers-tp4289p4290.html

To unsubscribe from Database Triggers, click here.
NAML

[ErmanArslansOracleBlog]

What error are you getting during compilation?

[ErmanArslansOracleBlog]

Also, set the following in your session and reproduce the problem in the same session.

ALTER SESSION SET EVENTS '942 trace name context forever, level 1';

Send me trace file after that..

[Phindile]

I just noticed that table DUMMY_PERSON has just been updated, it took very long to update thou.

Do you maybe know why it takes very long to update? If so how can one reduce the amount of time it takes to update.

Thanks.

On Tue, Nov 7, 2017 at 11:26 AM, ErmanArslansOracleBlog [via Erman Arslan's Oracle Forum] <[hidden email]> wrote:

What error are you getting during compilation?

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/Database-Triggers-tp4289p4292.html

To unsubscribe from Database Triggers, click here.
NAML

[ErmanArslansOracleBlog]

What?
you just said that you had compilation error?
So your update problem solved?

Please clarify that first.

After that, answer the following..

What update are you doing? How many rows are there in the table? What columns are there and what about the indexes on that table? How many indexes and what are the size of them?

[Phindile]

I got the following messages:

compiled (with errors)

and it's followed by another message which only says "compiled" without (with errors).

I did an insert in the PERSALNO column on the DUMMY_PER_GEN_DET table

I checked the DUMMY_PERSON table, and noticed that it had updated the PERSALNO column.

There's no indexes on the table, let me look into indexing right now.

On Tue, Nov 7, 2017 at 11:51 AM, ErmanArslansOracleBlog [via Erman Arslan's Oracle Forum] <[hidden email]> wrote:

What?
you say, you had compilation error?
So your update problem solved?

Please clarify that first.

After that, answer the following..

What update are you doing? How many rows are there in the table? What columns are there and what about the indexes on that table? How many indexes and what are the size of them?

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/Database-Triggers-tp4289p4295.html

To unsubscribe from Database Triggers, click here.
NAML

[ErmanArslansOracleBlog]

So?any updates?

[Phindile]

Well the trigger seems to be working, thanks man 

On 08 Nov 2017 10:03 AM, "ErmanArslansOracleBlog [via Erman Arslan's Oracle Forum]" <[hidden email]> wrote:

So?any updates?

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/Database-Triggers-tp4289p4324.html

To unsubscribe from Database Triggers, click here.
NAML