Forms :: Trigger To Check Field Value After Query?
Mar 8, 2010
What is the trigger which should be used to check certain field value after posting the query.
Example:
I have executed the query and the records are fetched. There is one field I want to check if it is null then it should be enabled, else, keep it disabled.
I have in the same form display item that its value will be sitting according to the post trigger of the previous text field when i press the sort button i get frm-40735 post-text-item unhandled exception Ora-01403 error.
I require to disable the record during the query, if the certain flag (Y) is activiate. And their as allow to display the next record without the flag (N) is editable.
I create a master detail form. I created also a post quey trigger, inside I wrote the code below.A cursor looks to another table and returns to the form some values i need!
declare cursor C1 is select apofaa, startdate, enddate, transport from trapofdet where trapofdet.apofcode = :trapof.apofcode; [code]....
How to solve the
a. frm-40737, illegal restricted procedure go block in post-query trigger b. It seems tha returns all the records but it appears only the last one.
i have a field in my table office i got field office_code ,this field is been used in diffirent tables as foreign key is there a sql i can wirte to see all tables who have used this field as foreign key.
I have an interesting problem. I have a server in the UK and I have three databases globally - UK (FINUKQ1), NY (FINNYQ1) and TK (FINTKQ1). The NY and TK databases will be migrated to the UK server as part of a consolidation project. Obviously, Time Zones and DST are the biggest concern for me.Here was my plan for the implementation (concentrating on Time Zone/DST issues).
Added to .profile: -------------------- if [[ "$ORACLE_SID" = "FINTKQ1" ]]; then export TZ="Asia/Tokyo" elif [[ "$ORACLE_SID" = "FINNYQ1" ]]; then export TZ="America/New_York" else unset TZ fi echo "TZ=$TZ"
Once environment has been set start database and listener.
Change database TZ: ----------------- alter database set time_zone='Asia/Tokyo'; alter database set time_zone='America/New_York';
Check database TZ: select dbtimezone from dual;
Add trigger: ------------ CREATE OR replace TRIGGER sys.timezone_check AFTER startup ON DATABASE DECLARE
[code]...
The trigger was there to put the database in restricted session if it is started in the wrong TZ and an error written to the alert log. The check was done on the dbtimezone and the dbname. Quite simple. Only issue is that it doesn't work as I wanted as dbtimezone and TZ are separate and disparate entities. i.e. I can start the TK database up in UK time (which will be incorrect for this project), and check the dbtimezone and it will show as 'Asia/Tokyo'.
Is there a better way of checking? Any trigger like this to compare TZs etc?
How can I create a trigger to check if synonym already exists in db and if exists then don't create synonym.
my work: ( this is just like an outline i prepared)
select * from all_synonyms; declare s_exists number; begin -- check whether the synonym exists select 1 into s_exists from all_synonyms; -- an error gets raise if it doesn't exception when no_data_found then -- DDL has to be done inside execute immediate ' create or replace synonym'; end; /
I know this is an old thread and I just started working with triggers. I want to check if the data being inserted is already in the table and if not insert it:
create or replace trigger t_triggername before insert on tbl_tablename for each row begin if(:new.user_id <> :old.user_id) then insert into tbl_tablename(user_id, location) values (:new.user_id, :new.location); end if; end;
what if I wanted to keep the user but only update location if the user is already in the table. I've tried doing it this way:
create or replace trigger t_triggername before insert on tbl_tablename for each row begin if(:new.user_id <> :old.user_id) then insert into tbl_tablename(user_id, location)
I have a form that contains some data and I would like to count them in a new "Item text" ... I don't want to use the Item Text's property where we set the data type to number, database to No, Query all record to Yes and etc... I want to use the SQL statement code to count the records (using trigger)... I named the Item Text as Count_Records .
I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a
select 'date_on' to add a field called date on populated by 'date_on' (the name of the column in the first query)
however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.
I have three tables. One for projects, one for volunteers, and a bridge entity for the many to many relationship between the Project and Volunteer.
In Project table, I have a field called, Volunteers_currently_signed_up, which means the number of volunteers currently signed up to participate in a project.
When I add an entry to my bridge entity which is composed of Volunteer_ID and Project_ID, I want the Volunteers_currently_signed_up to increment by 1, where the Project_ID in the bridge entity corresponds to that in Project.
I have very very little PL/SQL, and this is my amateur attempt so far:
CREATE OR REPLACE trigger "BI_Volunteers_currently_signed_up" BEFORE INSERT OR UPDATE ON Volunteers_in_project for each row WHERE Volunteers_in_project.Project_ID=Project.Project_ID; begin Project.Volunteers_currently_signed_up += 1; end; /
I have a table where i need to update one field values based on another field of the same table , simply as it is.I have done this using one select all check box , on clicking that all check boxes of item_trans table will get selected , then i will un select some of check box and then using one button, i will update the value of the fields which are checked only.
I have put the sample code but when i am updating its taking long time and hanging.I am also attaching the form based on the test case provided.
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE1',40,NULL); insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE2',20,NULL); insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE3',20,NULL);
--i want to set the value of trans_Act_qty as trans_qty
--i create one dummy or test block to keep the select all check box. for that table test script is
CREATE TABLE TEST ( C VARCHAR2(2000 BYTE), B NUMBER, A NUMBER );
insert into test (C,B,A) values ('A',1,1);
--code written in select all check box which is created on test.block.
BEGIN GO_BLOCK('item_trans'); FIRST_RECORD; LOOP :M_END_YN := :M_END_ALL; [code].......
--code written in M_END_YN ( actual check boxes where i will uncheck).
IF :M_END_YN = 'N' THEN :M_END_ALL := 'N'; END IF;
--code written on button to update those values which are checked.
BEGIN GO_BLOCK('item_trans'); FIRST_RECORD; LOOP IF :M_END_YN = 'Y' THEN [code]......
SELECT wmsys.wm_concat (gp.prog_acronym) FROM inf_program gp, ea_audit_program ap WHERE ap.sys_prog_id = gp.sys_prog_id AND ap.sys_audit_id =484
is there any way I can check the datatype of the result of the above query ? ,my dba added some patch to oracle , after the patch this query is returning a clob in java , it should return string and it used to return string before patch and in other databases it returns string, I can check the return type only from java side , is there any way oracle can say me the datatype ?
Need to check the elapse time for particular query ran from last 24 hours , it was ran multiple times and need to know for each execution what is elapse time .
I am having a Select query(below Query1) and I want to use one column(sum(col4)) from this Select query to be displayed in another Select query(Query 2). how to display this.
Query 1 :- select a.col1,a.col2,b.col3,sum(b.col4) from tab a, tab b where a.key1=b.key1 and a.key2=b.key2 group by a.col1,a.col2,b.col3
Query 2 :- select a.col1,a.col2,b.col3,sum(b.col6) from tab a, tab b where a.key1=b.key1 and a.key2=b.key2 group by a.col1,a.col2,b.col3,b.col5
I need to display the parameter and status of DB for listener and Read Only.
I know those value could be get from command line , but could we get the values of Listener and Read only by SQL/PlSQL? So I can get it through the query of DB.
How can i check if paging happening while running the query. As i have 4gb of PGA target but the query is taking long time in parallel and has hash join.
In employees table I am having employee_name column data as follows
Aaron, Mrs. Jamie (Jamie)Aaron, Mrs. Jenette (Jenette)Abbott, Ms. Rachel (Rachel) Breton, Mr. Jean Britz, Mrs. Sarie (Sarie) --> Now, I want to display the employee name like "Mrs. Jamie" (with out Surname and with out bracket included text),-->
create table FIELDS ( FIELD_NAME VARCHAR2(30) not null, PRG_FIELD NUMBER not null, LENGTH NUMBER );
with
INSERT INTO FIELDS VALUES('FIELD1', 1, 3); INSERT INTO FIELDS VALUES('FIELD2', 2, 3); INSERT INTO FIELDS VALUES('FIELD3', 3, 4); INSERT INTO FIELDS VALUES('FIELD4', 4, 2); INSERT INTO FIELDS VALUES('FIELD5', 5, 1);
I need to insert in a table:
create table STUFF ( FIELD_NAME VARCHAR2(30) not null, FSTART NUMBER not null, LENGTH NUMBER );
And the output I want is:
INSERT INTO STUFF VALUES('FIELD1',0,3); INSERT INTO STUFF VALUES('FIELD2',3,3); INSERT INTO STUFF VALUES('FIELD3',6,4); INSERT INTO STUFF VALUES('FIELD4',10,2); INSERT INTO STUFF VALUES('FIELD5',12,1);
So each field starts where the previous (ordered by PRG_FIELD asc) ends.
I think the query should use both lag and connect by but I haven't had any luck writing it. The problem is that all the examples I've seen around, using connect by prior, utilize 2 fields with different names, es connect by prior emp_id = mgr_id. Instead I should do something like connect by prior prg_field = prg_field-1 but that doesn't seem to work.
PS: I don't necessarily need to do this, I have a guy manually writing the inserts, this is just an exercise I would like to figure out