Home » SQL & PL/SQL » SQL & PL/SQL » trigger needed (OracleXE Apex 3.2)
trigger needed [message #424516] Fri, 02 October 2009 22:01 Go to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
CREATE SEQUENCE   "RR_HEAD_ID_SEQ"  MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
/


CREATE TABLE  "RR_HEAD_MST" 
   (	"HEAD_ID" NUMBER, 
	"HEAD_NAME" VARCHAR2(200), 
	"HEAD_TYPE" VARCHAR2(200), 
	 CONSTRAINT "RR_HEAD_MST_PK" PRIMARY KEY ("HEAD_ID") ENABLE, 
	 CONSTRAINT "RR_HEAD_MST_CK1" CHECK ("HEAD_NAME" IS NOT NULL) ENABLE,
         CONSTRAINT "RR_HEAD_MST_CK2" CHECK ("HEAD_TYPE" IS NOT NULL) ENABLE
   )
/

CREATE OR REPLACE TRIGGER  "BI_RR_HEAD_MST" 
  before insert or update on "RR_HEAD_MST"               
  for each row  
begin   
  if :NEW."HEAD_ID" is null then 
    select "RR_HEAD_ID_SEQ".nextval into :NEW."HEAD_ID" from dual; 
  end if; 
     :NEW."HEAD_NAME"   := UPPER(:NEW."HEAD_NAME");
     :NEW."HEAD_TYPE"  := UPPER(:NEW."HEAD_TYPE");
end; 

/
ALTER TRIGGER  "BI_RR_HEAD_MST" ENABLE
/

CREATE UNIQUE INDEX  "RR_HEAD_MST_IDX1" ON  "RR_HEAD_MST" ("HEAD_NAME", "HEAD_TYPE")
/

INSERT INTO RR_HEAD_MST (NULL,'CASH','LEDGER');
INSERT INTO RR_HEAD_MST (NULL,'STATE BANK OF INDIA','LEDGER');
INSERT INTO RR_HEAD_MST (NULL,'CANARA BANK','LEDGER');

INSERT INTO RR_HEAD_MST (NULL,'CASH AND BANK BALANCES','GROUP');
INSERT INTO RR_HEAD_MST (NULL,'TRIAL BALANCE','REPORT');
INSERT INTO RR_HEAD_MST (NULL,'PROFIT AND LOSS ACCOUNT','REPORT');
INSERT INTO RR_HEAD_MST (NULL,'BALANCE SHEET','REPORT');
INSERT INTO RR_HEAD_MST (NULL,'FUND FLOW','REPORT');


CREATE SEQUENCE   "RR_LINK_ID_SEQ"  MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
/

CREATE TABLE  "RR_LINK_MST" 
   (	"LINK_ID" NUMBER, 
	"LEDGER_NAME" VARCHAR2(200), 
	"GROUP_NAME" VARCHAR2(200),
        "REPORT_NAME" VARCHAR2(200), 
	
         CONSTRAINT "RR_LINK_MST_PK" PRIMARY KEY ("LINK_ID") ENABLE, 
	 CONSTRAINT "RR_LINK_MST_CK1" CHECK ("LEDGER_NAME" IS NOT NULL) ENABLE,
	 CONSTRAINT "RR_LINK_MST_CK2" CHECK ("GROUP_NAME" IS NOT NULL) ENABLE,
         CONSTRAINT "RR_LINK_MST_CK3" CHECK ("REPORT_NAME" IS NOT NULL) ENABLE
   )
/







CREATE OR REPLACE TRIGGER  "BI_RR_LINK_MST" 
  before insert or update on "RR_LINK_MST"               
  for each row  
begin   
  if :NEW."LINK_ID" is null then 
    select "RR_LINK_ID_SEQ".nextval into :NEW."LINK_ID" from dual; 
  end if; 
     :NEW."LEDGER_NAME"   := UPPER(:NEW."LEDGER_NAME");
     :NEW."GROUP_NAME"  := UPPER(:NEW."GROUP_NAME");
     :NEW."REPORT_NAME"   := UPPER(:NEW."REPORT_NAME");

end; 

/

ALTER TRIGGER  "BI_RR_LINK_MST" ENABLE
/

CREATE UNIQUE INDEX  "RR_LINK_MST_IDX1" ON  "RR_LINK_MST" ("LEDGER_NAME", "GROUP_NAME","REPORT_NAME")
/

CREATE UNIQUE INDEX  "RR_LINK_MST_IDX2" ON  "RR_LINK_MST" ("REPORT_NAME", "GROUP_NAME","LEDGER_NAME")
/





My requirement is as follows:

In link master if i enter ledger name should get validated with head_mst head_name and head_type should be 'LEDGER'

In link master if i enter group name should get validated with head_mst head_name and head_type should be 'GROUP'

In link master if i enter report name should get validated with head_mst head_name and head_type should be 'REPORT'


I achieved through page / form using Selected LOV with filter condition and using select list option.

Is it possible to create a trigger for this requirement so that even if anybody enters data on sql prompt, the data will get validated.
Re: trigger needed [message #424568 is a reply to message #424516] Sat, 03 October 2009 20:32 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
sure, but we aren't going to write it for you.

Depending upon your version of Oracle. You will have to write the trigger and see if it gives you and error or not.

But let me ask you, why have you not written it to try it rather than asking us if you can do it. You do not need our permission try. You have clearly put some effort into the triggers, keep going.

Good luck, Kevin
Previous Topic: Convertion long to clob
Next Topic: lag and lead only for numbers ?
Goto Forum:
  


Current Time: Tue Nov 12 23:06:08 CST 2024