trigger needed [message #424516] |
Fri, 02 October 2009 22:01 |
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 |
|
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
|
|
|