Home » SQL & PL/SQL » SQL & PL/SQL » Need trigger help unable to proceed (oracle 10g xe,xp)
Need trigger help unable to proceed [message #356783] Sun, 02 November 2008 06:18 Go to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
I am having two tables such as vou_header,vou_detail. The structures are described below:
My requirement is whenever i type 'PV' in hd_vou_type (master record) i require dt_fin_code (first detailed record) must contain selective values such as >= 'L2401' and <= 'L2599'.


CREATE TABLE  "VOU_HEADER" 
   (	"ID" NUMBER, 
	"HD_VOU_NO" NUMBER(16,0), 
	"HD_VOU_DT" DATE, 
	"HD_VOU_NARATE" VARCHAR2(100), 
	"HD_VOU_DEBIT" NUMBER(16,2), 
	"HD_VOU_CREDIT" NUMBER(16,2), 
	"HD_VOU_TYPE" CHAR(2), 
	"HD_VALID" CHAR(1), 
	 CONSTRAINT "VOU_HEADER_PK" PRIMARY KEY ("ID") ENABLE, 
	 CONSTRAINT "VOU_HEADER_CON" UNIQUE ("HD_VOU_NO") ENABLE
   )
/

CREATE OR REPLACE TRIGGER  "BI_VOU_HEADER" 
  before insert on "VOU_HEADER"               
  for each row  
begin   
  if :NEW."ID" is null then 
    select "id_seq".nextval into :NEW."ID" from dual; 
    --select nvl(vou_no,0)+1 into :new.hd_vou_no from ctrl_mst;
    --update ctrl_mst set vou_no = nvl(vou_no,0)+1;
  end if; 
end; 
/
ALTER TRIGGER  "BI_VOU_HEADER" ENABLE
/

CREATE TABLE  "VOU_DETAIL" 
   (	"ID" NUMBER, 
	"DT_VOU_NO" NUMBER(16,0) NOT NULL ENABLE, 
	"DT_FIN_CODE" CHAR(5) NOT NULL ENABLE, 
	"DT_SUB_CODE" CHAR(5), 
	"DT_VOU_NARATE" VARCHAR2(100), 
	"DT_VOU_DEBIT" NUMBER(16,2), 
	"DT_VOU_CREDIT" NUMBER(16,2), 
	 CONSTRAINT "VOU_DETAIL_PK" PRIMARY KEY ("ID") ENABLE
   )
/
ALTER TABLE  "VOU_DETAIL" ADD CONSTRAINT "VOU_DETAIL_FK1" FOREIGN KEY ("DT_VOU_NO")
	  REFERENCES  "VOU_HEADER" ("HD_VOU_NO") ENABLE
/
ALTER TABLE  "VOU_DETAIL" ADD CONSTRAINT "VOU_DETAIL_FK2" FOREIGN KEY ("DT_FIN_CODE")
	  REFERENCES  "FIN_MST" ("FIN_CODE") 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_TR1" 
AFTER
insert or update or delete on "VOU_DETAIL"
begin
update vou_header set hd_vou_debit = (select sum(nvl(dt_vou_debit,0)) from vou_detail 
where vou_detail.dt_vou_no = vou_header.hd_vou_no),
hd_vou_credit = (select sum(nvl(dt_vou_credit,0)) from vou_detail 
where vou_detail.dt_vou_no = vou_header.hd_vou_no);


end;


/
ALTER TRIGGER  "VOU_DETAIL_TR1" ENABLE
/



some trigger code will help me
Re: Need trigger help unable to proceed [message #356807 is a reply to message #356783] Sun, 02 November 2008 09:40 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
This may be one of those EDIT checks that can't easily be done via traditional triggers.

Quote:
1) you need to enforce edits across tables or across rows in the same table (in your case across tables).

2) you need to enforce edits across multiple physical events, but you can't really control the sequence of events or if they all even need to happen. In short you are not in command of the transaction flow so to speak.

In this situation, I have seen people use combinations of features in order to do the edit. But be warned, you really have to want this to make it work.

I have never done this myself, but I have seen people use a materialized view with a check constraint in such a way that the view is always empty and fails when a row exists in the view. You will have to live with the constraint being essentially "deffered" till commit time, and you will need a MVIEW that is refresh on commit capable as well as refresh fast in nature. The idea of course is to make it so that the database does the work while taking the least amount of space and least amount of time. Some situations also employ the use of Oracle Objects.

Here is some reading to help you with the idea. But again be forewarned, this is a very advanced technique.

Complex Constraints by Vadim Tropashko

If this does not satisfy you, then do a google for complex contraints and materialized views. Once more, I have had little cause to do this, but it does work when done correctly. But it is an advanced technique so you need to test the snot out of it and do a lot of reading before you use it in a production environment.

Good luck, Kevin

[Updated on: Sun, 02 November 2008 09:42]

Report message to a moderator

Re: Need trigger help unable to proceed [message #356853 is a reply to message #356807] Sun, 02 November 2008 21:26 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
kevin,

i also had the same view. Postings done by my present assistant on my user-id. But I remember the similar situation had been solved through trigger by my student. Since i do not remember, i use to control through form. For example whenever i create header i create the first record in detail too without amount. User need to give amount and narattion if any. It works if it is only one default code on first record. Here in this case choice between many codes applicable for first record. Any way thanks for your kind help and i will go through the suggested materials. Meanwhile if you find any allied trigger on this ground using row number of detailed record through after insert or update please post the solution. Once again thank you very much. Another vague rememberance Barbara suggested something on this topic too. But my search failed.
yours
dr.s.raghunathan
Re: Need trigger help unable to proceed [message #357016 is a reply to message #356783] Mon, 03 November 2008 09:42 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Vadim Tropashko has a book out:

Quote:
"SQL DESIGN PATTERNS"
ISBN-10 - 0-9776715-4-2
ISBN-13 - 978-0-9776715-4-2


Chapter Four has a section on Materialized Views used to enforce various kinds of constraints. Among them is an example of duplicating a dependent foreign key constraint. His example was such that each emp row must have at least one dept row. This seems to follow what you need. You want your header row to have at least one detail row and that detail row must additional restrictions.

I think the following code does it. You will have to check. Additionally, I am not certain if there are any issues as regards the WITH ROWID clause. You should try reading about that too.

SQL> drop materialized view mv_header_detail;

Materialized view dropped.

SQL> drop materialized view log on vou_detail;

Materialized view log dropped.

SQL> drop materialized view log on vou_header;

Materialized view log dropped.

SQL> drop table vou_detail;

Table dropped.

SQL> drop table vou_header;

Table dropped.

SQL> CREATE TABLE  "VOU_HEADER" 
  2     ( "ID" NUMBER, 
  3   "HD_VOU_TYPE" CHAR(2), 
  4    CONSTRAINT "VOU_HEADER_PK" PRIMARY KEY ("ID") ENABLE
  5     )
  6  /

Table created.

SQL> 
SQL> CREATE TABLE  "VOU_DETAIL" 
  2     ( "ID" NUMBER, 
  3   "DT_FIN_CODE" CHAR(5) NOT NULL ENABLE, 
  4    CONSTRAINT "VOU_DETAIL_PK" PRIMARY KEY ("ID") ENABLE
  5     )
  6  /

Table created.

SQL> create materialized view log on vou_header with rowid;

Materialized view log created.

SQL> create materialized view log on vou_detail with rowid;

Materialized view log created.

SQL> create materialized view mv_header_detail
  2  refresh fast on commit
  3  as
  4  select vou_detail.id detail_id, vou_header.id header_id
  5        ,vou_header.hd_vou_type,vou_detail.dt_fin_code
  6  ,vou_detail.rowid detial_rowid,vou_header.rowid header_rowid
  7  from vou_detail
  8      ,vou_header
  9  where vou_header.id = vou_detail.id(+)
 10  and case
 11         when vou_header.hd_vou_type = 'VP' and vou_detail.dt_fin_code(+) >= 'L2401' and vou_detail.dt_fin_code(+) <= 'L2599' then 1
 12      end = 1
 13  /

Materialized view created.

SQL> alter table mv_header_detail
  2  add constraint mv_header_detail_fk1 check(detail_id is not null)
  3  /

Table altered.

SQL> insert into vou_header values (2,'VP');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (MV_HEADER_DETAIL_FK1) violated


SQL> select * from MV_HEADER_DETAIL;

no rows selected

SQL> select * from vou_detail;

no rows selected

SQL> select * from vou_header;

no rows selected

SQL> 


Good luck, Kevin
Previous Topic: ORA-20000: Object ORDER does not exist or insufficient
Next Topic: Page Not Find Error
Goto Forum:
  


Current Time: Fri Dec 09 12:00:54 CST 2016

Total time taken to generate the page: 0.28372 seconds