Soln on a trigger needed

[deepa]

Can you give a back end solution here.
Requirement is : there is a master and a detail table. When a detail record changes, we want to check all other detail records and update master record.

For example: There is a Purchase Order header and detail tables. Suppose a PO's status is open (not all lines received yet, so balance of those lines is >0 ). User modifies the PO and updates PO detail line's cancelled qtys such that all or some line's balance becomes 0. The trigger should now check and update PO header's status as Closed if all lines on the PO have balance as 0. Is such a trigger possible without getting mutations?

[mkumarnk]

Hi,

Why u want to use trigger... U can call a simple stored procedure to do the updation of header status after cheking the balance while modifying the receiving qty...(i.e. i balalce is 0 update the status to closed).

Manoj

[slimdave]

Originally posted by mkumarnk
Hi,

Why u want to use trigger... U can call a simple stored procedure to do the updation of header status after cheking the balance while modifying the receiving qty...(i.e. i balalce is 0 update the status to closed).

Manoj

This sounds like great advice -- embedding business logic in triggers is a Bad Thing, because it obscures the way the application runs and makes it more difficult to support.

[deepa]

It's ok with me to shift the businesss logic to a stored procedure, but then, the procedure will also be called from a trigger only, because I want online updates, not scheduled.
So, is this solution possbile ?

[stecal]

Originally posted by slimdave
This sounds like great advice -- embedding business logic in triggers is a Bad Thing, because it obscures the way the application runs and makes it more difficult to support.

Which is contrary to what Oracle says:

How Triggers Are Used
Triggers supplement the standard capabilities of Oracle to provide a highly customized database management system. For example, a trigger can restrict DML operations against a table to those issued during regular business hours. You can also use triggers to:

Automatically generate derived column values
Prevent invalid transactions
Enforce complex security authorizations
Enforce referential integrity across nodes in a distributed database
Enforce complex business rules
Provide transparent event logging
Provide auditing
Maintain synchronous table replicates
Gather statistics on table access
Modify table data when DML statements are issued against views
Publish information about database events, user events, and SQL statements to subscribing applications

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c18trigs.htm

[slimdave]

Well strictly speaking this isn't a business rule, it's a denormalization -- what deepa wants to do is store redundant data in a master table.

That aside, all that Oracle Corp. is saying here is that triggers can be used for the listed purposes.

Now personally i think that it's a bad idea to hide application-essential code in a trigger. If your application depends on it then put it in the application packages, where support and development staff can see exactly where the code fits into the application.

[DaPi]

Originally posted by slimdave
Well strictly speaking this isn't a business rule, it's a denormalization -- what deepa wants to do is store redundant data in a master table. Interesting! I would say that since the Order Status is a function of other data it could be considered as de-normalisation. HOWEVER the nature of the function is a business rule and as such could change with time (e.g. perhaps later there could be a validation phase in the order closing process?). This example is on the dividing line.

From deepa's second post it sounds as if a trigger may be the only way to start the process (3rd party soft may not give the opportunity to do it any other way - I am faced with that). Is that right deepa? If not, then the quantities should always be modified via a proc, which sets the order status at the same time (as everyone else suggested).

I certainly can't argue with the desirability to keep all the business logic visible. If a process consists of A then B then C, it would not be clever to implement A & C in a block of code with B in a trigger.