Home » SQL & PL/SQL » SQL & PL/SQL » trigger required on strange requirement (Oracle 10G Apex)
trigger required on strange requirement [message #349559] Mon, 22 September 2008 04:06 Go to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi



CREATE TABLE  "VOU_HEADER" 
   (	"VOU_NO" NUMBER, 
	"VOU_DEBIT" NUMBER(16,2), 
	"VOU_CREDIT" NUMBER(16,2), 
	 CONSTRAINT "VOU_HEADER_PK" PRIMARY KEY ("VOU_NO") ENABLE
   )
/

CREATE OR REPLACE TRIGGER  "BI_VOU_HEADER" 
  before insert on "VOU_HEADER"               
  for each row  
begin   
  if :NEW."VOU_NO" is null then 
    select "VOU_HEADER_SEQ".nextval into :NEW."VOU_NO" from dual; 
  end if; 
end; 

/
ALTER TRIGGER  "BI_VOU_HEADER" ENABLE
/



CREATE TABLE  "VOU_DETAIL" 
   (	"ID" NUMBER, 
	"VOU_NO" NUMBER, 
	"ACCOUNT_HEAD" VARCHAR2(20), 
	"VOU_DEBIT" NUMBER(16,2), 
	"VOU_CREDIT" NUMBER(16,2), 
	 CONSTRAINT "VOU_DETAIL_PK" PRIMARY KEY ("ID") ENABLE
   )
/
ALTER TABLE  "VOU_DETAIL" ADD CONSTRAINT "VOU_DETAIL_CON" FOREIGN KEY ("VOU_NO")
	  REFERENCES  "VOU_HEADER" ("VOU_NO") ENABLE
/

CREATE OR REPLACE TRIGGER  "BI_VOU_DETAIL" 
  before insert on "VOU_DETAIL"               
  for each row  
begin   
  if :NEW."ID" is null then 
    select "ID_SEQ".nextval into :NEW."ID" from dual; 
  end if; 
end; 

/
ALTER TRIGGER  "BI_VOU_DETAIL" ENABLE
/

CREATE OR REPLACE TRIGGER  "VOU_DETAIL_T1" 
AFTER
insert or update or delete on "VOU_DETAIL"
for each row
begin
update table vou_header set vou_header.vou_debit = (select sum(vou_detail.vou_debit) from vou_detail where vou_detail.vou_no=vou_header.vou_no),
vou_header.vou_credit = (select sum(vou_detail.vou_credit) from vou_detail where vou_detail.vou_no=vou_header.vou_no)
end;
/
ALTER TRIGGER  "VOU_DETAIL_T1" ENABLE
/


select * from vou_header


vou_no 	vou_debit 	vou_credit
1001	100.00		100.00
1002	1234.56		1234.56
1003	2000.00		2000.00

select * from vou_detail

id	vou_no	account_head	vou_debit	vou_credit
2631	1001	cash		100
2632	1001	sales		null		100
2633	1002	cash		150.00
2634	1002	discount	 30.00
2635	1002	bank		1054.56
2636	1002	commisssion			200.00
2637	1002	sales			        1034.56
2638	1003	purchase	2000.00
2638	1003	discount			200.00
2638	1003	cash				1800.00





Now I have peculiar requirement.

In apex tabular column allows me to add record through ADD NEW button. It does not write it in vou_detail.
Once I give apply changes, it will insert the records in vou_detail table. At the time of insertion, through trigger
I am updating vou_header file vou_debit and vou_credit. My requirement is

After updating the vou_header.vou_debit and vou_header.vou_credit, if both the columns are not equal,
changes made in vou_header should be discarded and as well as any insertion / updation effectedin vou_detail also should be
cancelled.

How to write a trigger? and on which table?

yours
dr.s.raghunathan
Re: trigger required on strange requirement [message #349563 is a reply to message #349559] Mon, 22 September 2008 04:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is typically the kind of things that must not be done by triggers.
Read The Trouble with Triggers

In addition, aggregate columns are a bad idea in a table, use a mview on commit refresh instead.

Regards
Michel
Re: trigger required on strange requirement [message #349564 is a reply to message #349563] Mon, 22 September 2008 04:20 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
dear micheal,

thank you very much for strengthening my views too. Though i had instructed my subordinate inline with your suggestions, i just want to probe on anyother views to have better understanding and refining my thoughts.
once again thank you very much
yours
dr.s.raghunathan
Re: trigger required on strange requirement [message #349566 is a reply to message #349559] Mon, 22 September 2008 04:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do those triggers actually work?
To my eyes, VOU_DETAIL_T1 has 'Mutating Table Error' written all over it
Re: trigger required on strange requirement [message #349567 is a reply to message #349559] Mon, 22 September 2008 04:29 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I agree with Micheal's Openion .

Anyway , Rolling back of the main DML statement will Cancel the triggered effect also .

Thumbs Up
Rajuvan.
Re: trigger required on strange requirement [message #349569 is a reply to message #349563] Mon, 22 September 2008 04:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, instead of creating a Mview, you could just create a standard view on the Header table that will calculate and show the aggregate values on the fly.
Re: trigger required on strange requirement [message #349573 is a reply to message #349569] Mon, 22 September 2008 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, mview is only necessary if data become large and it will be too long to calculate the aggregate on the fly.

Regards
Michel
Re: trigger required on strange requirement [message #349575 is a reply to message #349569] Mon, 22 September 2008 04:39 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
dear JRowBottom,
i have not tried Mview still. However aggregating values for simple view alone is not my requirement. When header debit and credit does not tally, i want to dispense the entries effected in vou_detail. Still my collegue differs on this opinion and strongly feels that triggers will be very helpful and worked fine in some place. Only thing he could not remember it. He is still in serch of solutions on other sites.

let me be patient to wait for the outcome.
yours
dr.s.raghunathan
Re: trigger required on strange requirement [message #349582 is a reply to message #349559] Mon, 22 September 2008 05:00 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Nobody has stated that Triggers wont work fine in your case . It will work fine 100% . But it is not good practice to use it for such kind of stuffs.

And if you really insist to use triggers , there is another alternative ( instead of Aggregate function) .

In case of Insert ,

Add the inserted debit and credit amount from detail to header (like x = x + new x)

In case of delete,

Substract the deleted debit and credit amount from detail to header (like x = x - new x)

In case of update,

Substract the old debit and credit amount and add new debit and credit amount to header (like x = x - old x + new x )

And if both amounts are not equal , just rollback the DML operation ( preferrably at appln level ), so that triggered effect also will be cancelled .

Thumbs Up
Rajuvan.



Re: trigger required on strange requirement [message #349583 is a reply to message #349575] Mon, 22 September 2008 05:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I strongly suspect that yuor colleague is wrong.
Even if you only ever inserted one value ata time using the Insert Into... Values approach to get round the mutatin table, it will still fail in a multi=user environment.

Can you explain what you are looking for in a bit more detail?

why would you expect the credit and debit columns to be equal (unless you're using the terms in a very non-accountancy fashion.

Further, the way these triggers will fail is not by the Db and Cr columns being different, but by them not being equal to the sum of the detail records.
Addmittedly, they will fix themselves as soon as a single transaction goes through withouth any multi-user problems, but you have to ask yourself how often the data in them is allowed to be wrong.
Re: trigger required on strange requirement [message #349586 is a reply to message #349582] Mon, 22 September 2008 05:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Nobody has stated that Triggers wont work fine in your case .

I stated and repeat that.
When aggregate is in place, triggers only work in single-user environment or if you lock the tables.

Regards
Michel
Re: trigger required on strange requirement [message #349591 is a reply to message #349559] Mon, 22 September 2008 05:27 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

@ Micheal ,

Let me also repeat the same

Quote:
Nobody has stated that Triggers wont work fine in your case


I didn't say that

Quote:
Nobody has stated that Aggregate Triggers wont work fine in your case


Thats why i suggested a new method of tackling it.

This is working perfectly fine in our application (multi-user) for the last 6 years.

Thumbs Up
Rajuvan.
Re: trigger required on strange requirement [message #349593 is a reply to message #349583] Mon, 22 September 2008 05:29 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
dear JRowBottom,

Quote:


why would you expect the credit and debit columns to be equal (unless you're using the terms in a very non-accountancy fashion




vou_header tables cumulates the the debit and credit available in vou_details. Once the header debit = credit then it is assumed that given vouchers is in complete form under double entry system. otherwise this voucher need to be discarded in all the reports of ledger, trial balance, Profit and loss account and balance sheet.

Further vou_header and vou_details have some more columns in the nature of one time entry and multiple entry. For example vou_header will have vou_naration which will store the nature of voucher in brief. whereas vou_naration available in vou_detail will get stored reflection of all the account_head behaviour. I have not inserted the naration columns since my problem do not pertaining to these columns. Hope i am clear on above quoted text.


Quote:

Values approach to get round the mutatin table,



There is one advantage that i will not mutatin problem since addnew button on apex will simply store the data's in row form. Only when i press apply changes, it starts writing into a table hence i do not forsee the problem mutating effect. of course still if debit and credit does not tally then mutating problem may occur with different ora error.

Additional information i try to insert data in voucher_detail table through Master detail.

My try made to insert record in vou_header and there are occasions that no data available in dvou_detail also. where as
my specific requirement is after inserting multiple records in vou_detail, if sum of vou_detail debit and vou_detail credit does not tally, then i do not want to have vou_header record too and as well in vou_detail. Further if records already exists in vou_header and vou_detail, recent changes effected only should be roll backed.

As rajuvu1 suggested i tried to write procedures for inserting,updating and deleting was effected. It worked out with
Inverntory master and inventory detail where issues, purchase were made as separate entry. when i tried to approach this on present requirement, it is not working since multiple values involved.

I have one other solution to control the effect and inserting one column in vou header for valid voucher no. and can try.

As Michel suggested i also decided that it is not possible due to multiple values. Now that rajuvu1 and yourself have open some other windows to probe, i will try those areas too.

yours
dr.s.raghunathan




Re: trigger required on strange requirement [message #349597 is a reply to message #349593] Mon, 22 September 2008 05:42 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I see what you're trying to do - you want to post a complete set of debits and credits at once, and if the total value of the credits on file and the debits on file is not the same, then you want to roll back the current set of changes.

To do this, you need a statement level trigger, rather than a row level trigger, as you need to consider all of the rows inserted in the transaction, rather than looking at each row as it is inserted.

You still won't be able to guarantee that the totals on the header match the sum ov the values on the details, but you should be able to ensure that only sets of rows where the debit and credit totals match are inserted.
Previous Topic: Simple Query
Next Topic: Another way to write hierarchical query...???
Goto Forum:
  


Current Time: Fri Dec 02 18:57:06 CST 2016

Total time taken to generate the page: 0.33128 seconds