| POST firing DB triggers [message #673076] |
Mon, 05 November 2018 05:01  |
 |
RaShi.Raj
Messages: 84
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  |
cookiemonster
Messages: 13961
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 #673081 is a reply to message #673080] |
Mon, 05 November 2018 05:31 |
cookiemonster
Messages: 13961
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 |
cookiemonster
Messages: 13961
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;
If you run this in sqlplus:
UPDATE tab set col1 = value,
col2 = value,
col3 = value,
col4 = value,
col5 = value
where .....
commit;
Forms is no different.
[Updated on: Mon, 05 November 2018 05:35] Report message to a moderator |
|
|
|
|
|
|
|
| Re: POST firing DB triggers [message #673086 is a reply to message #673084] |
Mon, 05 November 2018 07:06 |
|
RaShi.Raj
Messages: 84
Registered: May 2014
Location: Bangalore
|
Member |
|
|
cookiemonster wrote on Mon, 05 November 2018 18:10Why 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).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
