OraFAQ Forum: Forms » POST firing DB triggers

Home » Developer & Programmer » Forms » POST firing DB triggers (Forms 11G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: POST firing DB triggers [message #673077 is a reply to message #673076]

Mon, 05 November 2018 05:08

cookiemonster
Messages: 13966
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?).

Report message to a moderator

Re: POST firing DB triggers [message #673079 is a reply to message #673077]

Mon, 05 November 2018 05:14

cookiemonster
Messages: 13966
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?

Report message to a moderator

Re: POST firing DB triggers [message #673080 is a reply to message #673079]

Mon, 05 November 2018 05:27

RaShi.Raj
Messages: 84
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.

Report message to a moderator

Re: POST firing DB triggers [message #673081 is a reply to message #673080]

Mon, 05 November 2018 05:31

cookiemonster
Messages: 13966
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.

Report message to a moderator

Re: POST firing DB triggers [message #673082 is a reply to message #673081]

Mon, 05 November 2018 05:35

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

Think of it this way.
If you run this in sqlplus:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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

RaShi.Raj
Messages: 84
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.

Report message to a moderator

Re: POST firing DB triggers [message #673084 is a reply to message #673083]

Mon, 05 November 2018 06:40

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

Why is changing forms not feasible at this point?

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: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).

Report message to a moderator

Previous Topic:	Oracle form Builder
Next Topic:	Open OBIEE report from Oracle form 12c

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 00:56:49 CDT 2025