Home » Developer & Programmer » Forms » POST firing DB triggers (Forms 11G)
POST firing DB triggers [message #673076] Mon, 05 November 2018 05:01 Go to next message
RaShi.Raj
Messages: 82
Registered: May 2014
Location: Bangalore
Member
Hi All,

In the form code i have a POST command which fires a DB trigger (before update). In the DB trigger i'm inserting data into some backup table which creates lot of backup records every time POST is fired. I want this DB trigger to fire only on COMMIT (not on POST). Is there any way to do it? I have a workaround but before implementing it would like to know if there is a straight and easy method of doing this.

Thanks,
Rashi
Re: POST firing DB triggers [message #673077 is a reply to message #673076] Mon, 05 November 2018 05:08 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
No.

Post tells oracle to issue the appropriate insert/update/delete commands to the DB.
DB triggers fire when those commands are run.

If you issue an insert in sqlplus any insert triggers on the table will fire at that point, not when you issue commit.
Forms is no different.

Question is - why is this a problem for you? It's standard behaviour, if the form rollbacks instead of committing then all the work done by the triggers will be rolled back as well (unless you're using autonomous transactions. And if you are, why are you doing that?).
Re: POST firing DB triggers [message #673079 is a reply to message #673077] Mon, 05 November 2018 05:14 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Put another way - why do you think having the trigger fire on commit will give a different amount of backup records to it firing on post?
Re: POST firing DB triggers [message #673080 is a reply to message #673079] Mon, 05 November 2018 05:27 Go to previous messageGo to next message
RaShi.Raj
Messages: 82
Registered: May 2014
Location: Bangalore
Member
cookiemonster wrote on Mon, 05 November 2018 16:44
Put another way - why do you think having the trigger fire on commit will give a different amount of backup records to it firing on post?
Because i want to backup the data only on final commit with just one backup record.

For e.g. I have 5 fields on a screen and i change values in each of those fields by going back and forth (closing the screen fires POST).
This results in 5 records in the backup table. All i want is just one backup record with all 5 changes.

Re: POST firing DB triggers [message #673081 is a reply to message #673080] Mon, 05 November 2018 05:31 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
So change the form so it only issues one update statement when you change the fields, rather than 5 updates, which is presumably what it's doing at the moment.

POST vs COMMIT is not the problem.

The form issuing more DML statements than necessary to get the job done is the problem.
Re: POST firing DB triggers [message #673082 is a reply to message #673081] Mon, 05 November 2018 05:35 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Think of it this way.
If you run this in sqlplus:
UPDATE tab SET col1 = value WHERE ....
UPDATE tab SET col2 = value WHERE ....
UPDATE tab SET col3 = value WHERE ....
UPDATE tab SET col4 = value WHERE ....
UPDATE tab SET col5 = value WHERE ....
commit;
The update trigger will run 5 times and you'll get 5 trace records.
If you run this in sqlplus:
UPDATE tab set col1 = value,
               col2 = value,
               col3 = value,
               col4 = value,
               col5 = value
where .....
commit;
The trigger will fire once and you'll get 1 trace record.

Forms is no different.

[Updated on: Mon, 05 November 2018 05:35]

Report message to a moderator

Re: POST firing DB triggers [message #673083 is a reply to message #673082] Mon, 05 November 2018 06:23 Go to previous messageGo to next message
RaShi.Raj
Messages: 82
Registered: May 2014
Location: Bangalore
Member
Yup, i got it. Thank you. Since it's not feasible to change forms at this point, looking for other options to handle this.
Re: POST firing DB triggers [message #673084 is a reply to message #673083] Mon, 05 November 2018 06:40 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why is changing forms not feasible at this point?
Re: POST firing DB triggers [message #673086 is a reply to message #673084] Mon, 05 November 2018 07:06 Go to previous message
RaShi.Raj
Messages: 82
Registered: May 2014
Location: Bangalore
Member
cookiemonster wrote on Mon, 05 November 2018 18:10
Why is changing forms not feasible at this point?
This form will go live soon. Quite a complex GUI to change at this point and test before that. Anyway, we are capturing some unique ID (from DB sequence) every time we enter this form. On first change (POST) we insert into the backup table along with this ID. On subsequent changes we'll check the current value of this to the one in the backup table (MAX(ID)). Then insert to backup table only if ID's are not equal (this means we are in a new session) and update if it's equal (in the same session).
Previous Topic: Oracle form Builder
Next Topic: Open OBIEE report from Oracle form 12c
Goto Forum:
  


Current Time: Thu May 06 14:18:37 CDT 2021