SQL & PL/SQL :: Populate Mypo Inside Trigger?

Apr 15, 2011

CREATE TABLE TBL_LTEST
(
ID NUMBER,
MYPO NUMBER
);

alter table tbl_ltest add constraint pk_tbl_ltest primary key (id, mypo);

I want the data in this table to be like this:

ID MYPO
1000 401
1000 402
1000 403
2000 401
2000 402
2000 403

Rule of MYPO column population:

1. It should always start with 401 (I can handle that with Oracle check constraint that mypo should be > 401)
2. For each Id, the first insert has to be 401, then it should add one from there within the Id

(something like, mypo is a sequence within the ID.)

I am trying to populate mypo inside the trigger in this way:

CREATE OR REPLACE TRIGGER trigTBL_LTEST
BEFORE INSERT OR UPDATE
ON TBL_LTEST
REFERENCING NEW AS NEW OLD AS OLD

[code]...

View 3 Replies


ADVERTISEMENT

Creating A Trigger To Populate Another Table

Jan 4, 2007

Having trouble creating a trigger to populate another table.

The SQL:

CREATE OR REPLACE TRIGGER "P_M_YES"
AFTER INSERT OR UPDATE ON DOMAIN
REFERENCING NEW AS NEW.P_M AND OLD AS OLD.P_M
FOR EACH ROW
WHEN (NEW.P_M = YES)
BEGIN
INSERT INTO PAGE_MAKER VALUES(:NEW.D_NAME, :NEW.USER_ID);
END P_M_YES;

/
ALTER TRIGGER "P_M_YES" ENABLE
/

I get an invalid trigger specification.

View 3 Replies View Related

Create Trigger On Table / Populate With Data From Additional Tables

Nov 7, 2010

I would like to create a trigger on a table which populates a log table. In addition to using the table where the trigger will exist, I would like to populate a couple more fields in the log table with with data from 2 other tables.

e.g.

NAME_TABLE
-reg_id
-name

ADDRESS_TABLE *trigger to be fired when a new record is created here.
-reg_id
-srv_id

PROCESS_TABLE
-srv_id
-start_time
-end_time

This is what I would like the logging table to look like:

LOGGING_TABLE
-address_table_reg_id
-address_table.srv_id
-name_table.name
-process_table.start_time
-process_table.end_time

How can I go about creating this type of trigger?

View 2 Replies View Related

Forms :: Master / Detail Relation / Why Populate-details Trigger Not Being Fired

Mar 14, 2013

On a form I have 2 blocks, shown on different tabs. Relation created by forms wizard. The relation works great when navigating through the blocks, the details for the master are shown fine.

Example for this form is master block is a type of food. Detail block is list of ingredients.When creating a new type of food, if it's = CAKE we insert records based on a select on another table into the ingredients table.Else, the user has to manually create the ingredients.

Upon commit of the form on master block (must be saved to enable detail block tab), the ingredients are not displayed but are there. If I execute query by hitting F8, the records are present.

Why is the populate-details trigger not being fired or what am I missing?

View 4 Replies View Related

SQL & PL/SQL :: Procedure Call Inside Trigger?

Aug 8, 2011

I am trying to call procedure inside trigger.. but i get error ora-04098 ..

create table emp_hstry
as
select * from emp
where
1= 2 ;

create or replace procedure emp_del_hstry(v_empno NUMBER ,
v_ename VARCHAR2,
v_job VARCHAR2)
is
insert into emp_hstry (empno,ename,job)
values (v_empno,v_ename,v_job);
COMMIT;
end;

create or replace trigger emp_del_hstry1
after insert or delete
on emp
for each row
begin
if deleting then
emp_del_hstry(:old.empno,:old.ename,:old.job);
end if;
end;

delete from emp
where
empno = '7369'

AFTER delete statement run i get ora-04098 message i also check show error command ,but still i am not getting solution of this error ..

View 7 Replies View Related

Possible To Create Sequence Inside DML Trigger

Apr 12, 2013

i would like to create a sequence inside a dml trigger. Is it possible? I created a trigger and it is compiled sucessfully.

create or replace trigger tri_update_test
after delete on test
declare
pragma autonomous_transaction;
begin
execute immediate 'create sequence t_unqid
start with 1
increment by 1
nocache '

end ;trigger created successfully. And i try to delete data from the test delete from test where id=5;

Output:

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TRI_UPDATE_TEST", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRI_UPDATE_TEST'

View 9 Replies View Related

Executing A Job Using DBMS_SCHEDULER Inside A Trigger

Oct 10, 2013

I would like to execute an external batch file in Windows using DBMS_SCHEDULER in an Oracle database (10g or a higher version). I intend this to be done through a trigger, which would be called on encountering specific errors in Oracle.

The problem is that I can't create triggers on SYS objects, but DBMS_SCHEDULER is owned by  (and as far as I understand, supposed to be used with) SYS user. what permissions need to be granted to which user? Also, can this be done on creating a new user and not using the default SYS and SYSTEM users? 

View 11 Replies View Related

SQL & PL/SQL :: Invoking Statement Inside The Trigger Body

Jan 11, 2012

I need to find out the exact DML statement which invoked a particular trigger inside a trigger body.

View 7 Replies View Related

SQL & PL/SQL :: Insert With Select And Subquery Inside Trigger

May 5, 2011

I hit a bottleneck where my insert trigger won't execute the insert statement (with subquery). See illustration below:

Step# 1 - Table definition:
Table_A(a1 number, a2 varchar(10), a3 varchar(10))
Table_B(b1 number, b2 varchar(10), b3 varchar(10))
Table_C(c1 number, c2 varchar(10), c3 varchar(10))

Step# 2 manipulated the tables:
Inserted 3 records in Table_C.

Then I created an Insert Trigger to Table_A with an insert statement into Table_B and a subquery to Table_C. Please see below:

CREATE OR REPLACE TRIGGER TABLE_A_TR
after INSERT OR UPDATE OR DELETE ON TABLE_A
FOR EACH ROW
DECLARE
[code]......

Step# 3 compiled the created trigger and I've successfully compiled it.
Step# 4 Tested the trigger (TABLE_A_TR) using an insert statement to TABLE_A.
Insert into TABLE_A values (1,'testa','testb')

I've successfully insert the values into TABLE_A however I've observed that the trigger didn't execute the insert statement because TABLE_B has an empty rows. I tried to manually execute the insert statement just to see if there's an issue in my insert statement but I've successfully populated the values into TABLE_B. So I'm wondering why the trigger didn't execute the insert statement.

View 4 Replies View Related

SQL & PL/SQL :: Usage Of Context Variables Inside The Trigger?

Feb 17, 2010

Am trying to use the SYS_CONTEXT variables inside a trigger. After reading the documentation I found that the below query when used inside the trigger code will return the trigger owner.

Select Username from User_Users

Will any of the below variables return the trigger owner when used inside the trigger code

sys_context('USERENV','CURRENT_USER')
or
sys_context('USER','CURRENT_SCHEMA')

View 13 Replies View Related

PL/SQL :: Dynamic Execution Of Block Inside Trigger?

Jan 3, 2013

Im using the following oracle database.

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

I have one problem in trigger execution. I have a small plsql block in trigger and, I want to execute it as a dynamic way. but it is giving the error. Please find the trigger code. Here my intension is that, the column name used in trigger should be dynamic. In future, if I want to switch the column name, I have to do without modification in trigger.
The error im getting is "ORA-01008: not all variables bound".

CREATE OR REPLACE TRIGGER ETM_AR_IU
AFTER UPDATE ON
EXTERNAL_MAPPING
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

[code]...

View 17 Replies View Related

SQL & PL/SQL :: How To Populate A Table

Oct 26, 2010

I have a table like this

Desc Salesperson;
Emp_name varchar2(20),
emp_id number,
begin_date date,
end_date date,
is_current varchar2(3)

This is the first time the table is getting loaded. I get the distinct emp name from table 'X' and insert into teh sales person table to list out all teh emp present in the company and give the corresponding details.

I am not sure how to write a proc to populate the whole table. emp id is sequence generated.

begin date any date say 10/01/2010 (mm/dd/yyyy)
end date is 99/99/9999 indicating that the emp is not terminated
is_current flag = 'Y' if end date is 99/99/9999.

I have done a distinct empname from table 'X' and inserted into the salesperson table. Do i proceed to update the table for individual columns? *is that the right approach*?

View 4 Replies View Related

SQL & PL/SQL :: Populate Tables Of Ref

Apr 12, 2012

I like to the following:

Objects: Car, Person

Person can own a lot of cars which i like to implement as table of ref in person!

When i poluplate the ref table, I always get null-references! Compiler is ok, no syntax errors or anything else

drop table person_tab;
drop table car_tab;
drop type person_type force;
drop type car_type force;
drop type owns_type force;

---------------------------Objects-----------------------------

create or replace type car_type as object
(cartype varchar2(10),
byear integer
);
/

create or replace type owns_type
as table of ref car_type;
/

create or replace type person_type as object
(fname varchar2(10),
owns owns_type
);
/

commit;

--------------------------Object Tabs--------------------------

create table person_tab of person_type
(fname primary key)
nested table owns store as owns_cars;
/

create table car_tab of car_type
(cartype Primary Key
);
/

commit;

------------------------Populate Tables------------------------

insert into person_tab values
('meier', owns_type());
/
insert into car_tab values
('audi', 2011);
/
insert into car_tab values
('vw', 2012);
/
insert into car_tab values
('bmw', 2011);

commit;

----------------------Populate Ref Tables----------------------

insert into table
(select p.owns
from person_tab p
where p.fname = 'meier')
select ref (c)
from car_tab c
where c.cartype = 'audi';
/

insert into table
(select p.owns
from person_tab p
where p.fname = 'meier')
select ref (c)
from car_tab c
where c.cartype = 'vw';
/
commit;

View 15 Replies View Related

Query One / Several Column(s) To Populate Another?

Jun 16, 2009

I need to query a table to read the value, specifically a date, in one column and characters in another, i.e. and ID number, and populate a new column with new data. For example:

If Column A = 1/1/2009 and Column B = 0123 in table 'Persons' I need a query statement to populate Column Z with a 'Yes'

If Column A = 2/1/2009 and Column B = 9876 in table 'Persons' I need a query statement to also populate Column Z with a 'Yes'

View 3 Replies View Related

Forms :: Populate Record Using LOV Value?

Mar 2, 2011

Create a form based on the following output use EMP Table

Create a non Database Block i.e Control Block----> Dept No

Create a Database Block -EMP
Create an LOV for the Dept no from dept table.
For the Current Dept No . Populate the Employee Records

View 3 Replies View Related

Forms :: Populate POPLIST?

Apr 4, 2011

-- Solution populate poplist
----------------------------
-- Name of block: block01
-- Name of poplist item: year
-- Name table: tb_years
-- Create on trigger when-new-form-instance

[code]...

View 3 Replies View Related

Forms :: FMB - Populate Lov Is Not Working?

Oct 14, 2010

.fmb file why this populate lov is not working

View 9 Replies View Related

Forms :: Populate Only Particular Employee LOV

Apr 1, 2013

I want to populate only particular employee LOV ,when i am press "Populate LOV"..Button and i attached Form for testing and It is a 10g version..

View 5 Replies View Related

Getting List Of Populate Tables In A Database?

Feb 9, 2012

The database system they have has around 5000 tables. The majority of these are not used (it is an off the shelf package). What I am trying to do is get a list of all tables in the database BUT only those that contain records (not the empty tables) and then copy this to an excel spreadsheet. We use a tool called aquadata to run sql enquiry statements on.

View 5 Replies View Related

SQL & PL/SQL :: How To Populate Distinct Column In A Table

Apr 22, 2013

I had to create a new column in a particular table now i want to insert the values in that column though the other columns are already populated I entered the command (insert into Product(STANDARD_PRICE) values(895.99) when i hit return it says cannot enter null value into (SYSTEM .PRODUCT. PRODUCT_ID) product_id is the PK which is the first column STANDARD_PRICE is the last column in my table...how do i enter the values into that column without receiving this error or having to effect the other columns?

View 3 Replies View Related

Forms :: Populate List Dynamically?

Mar 15, 2013

i want to create the list item dynamically on the when new form instance this is the code....

group_id := create_group_from_query('lst','select (imt.item_id), to_char(imt.item_code, from items_mt imt');
v_num := Populate_group(group_id);
populate_list(v_num,group_id);

but the list is not the populate.

View 14 Replies View Related

Forms :: Populate Table From A Block In 10g?

Dec 3, 2010

TABLE_FROM_BLOCK built-in Package is working in Forms 6i but not in Forms 10g. is there any other built -in available instead of this. how do we populate table from a block in Forms 10g?

View 6 Replies View Related

SQL & PL/SQL :: Dynamic Create And Populate Table

Jan 27, 2012

I am dynamically creating a staging table my_stg, and then populate it. Seems simple, but not sure why i get this error,

create table gtest4(myid varchar2(10), mykey varchar2(10));
create table gtest5(myid varchar2(10), mykey varchar2(10));
insert into gtest4 values(1,3);
insert into gtest4 values(2,7);
insert into gtest5 values(5,3);
insert into gtest5 values (1,7);
commit;

CREATE OR REPLACE PROCEDURE px
IS
TYPE rectype IS RECORD (
myid VARCHAR2 (100),
mykey VARCHAR2 (100)
);

TYPE tabtype1 IS TABLE OF varchar2(100)
INDEX BY BINARY_INTEGER;

TYPE tabtype2 IS TABLE OF varchar2(100)
INDEX BY BINARY_INTEGER;

rec1 tabtype1;
rec2 tabtype2;
cur sys_refcursor;
[code]....

count current exists max min prior sql stddev sum varianc execute forall merge time timestamp interval date

pipe
<an alternatively-quoted string literal

View 11 Replies View Related

Forms :: Populate Data Block

Aug 14, 2013

i have created on query block , upon pressing the button there is a wehre clause in the block which will filter the records based on all alike items from two tables but my problem is , in one table there is no information of struct and in other there is struct information or data, what i want is even if i pass a parameter in the where clause all the records should be filtered.

CREATE TABLE OT_ACTUAL_ACT (ITEM_CODE VARCHAR2(30),ITEM_NAME VARCHAR2(30),WO VARCHAR2(12),STRUCT VARCHAR2(12))
INSERT INTO OT_ACTUAL_ACT(ITEM_CODE,ITEM_NAME,WO,STRUCT)
VALUES ('1001','HEA100X10','300',null);

[Code]...

I am getting this

ITEM_CODEITEM_NAMEWOSTRUCT
1002 HEA100X2230030010

i want all the records

ITEM_CODEITEM_NAMEWOSTRUCT
1002 HEA100X2230030010
1003 HEA100 300
1001 HEA100X10 300

View 1 Replies View Related

SQL & PL/SQL :: Populate Column Two Times With Different Criteria?

Mar 22, 2011

I am trying to query O/P from two tables.Hence, populate same column(Level_Name) two time on report based on its Level

Table =Level_Mst

Codeno Level_Name Parent_level
1 IT Dir 0
2 HR Dir 0
3 Assets Section 1
4 Payroll Section 2

Table=Users

User_id Name Top_level Bottom_Level
1 John 1 3
2 Smith 2 4

Desired O/P

Name Top_lvl Bottom_lvl Top_lvl_Name Bottom_Lvl_Name
John 1 3 IT Dir Assets_Section

Right now im getting name from level_mst either for top_level or for bottom_level .I want to show both names in one row.

View 6 Replies View Related

Forms :: Populate List Dynamically

Mar 3, 2011

I've got a situation where I need to populate a list box of lab numbers when the user selects a matching date received date.

Right now it only works when the user presses enter on the field used to accept a date.

It also works when a user fills in date and then clicks twice on the listbox of lab numbers. (assuming there are some records for the date in question).

Both fields are in a data block that is not connected to a table. Here is the when-mouse-doubleclicked trigger

LAUNCH_CALENDAR('DISPLAY.DATE_BEAN');
IF :DISPLAY.DATE_RECEIVED IS NOT NULL THEN
GO_ITEM('display.list_labno');
--POPULATE_LIST_ITEMS('date_received_records','display.list_labno');
do_key('list_values');

[Code]....

As you can see we have tried several different triggers to get this to work with a calendar date chooser (java bean). The bean works and allows for a date to be returned. However, it does not want to populate the listbox.

View 5 Replies View Related

Forms :: Populate Record In Data Block

Jul 14, 2010

My Requirements:

1. i want to create form for student's attendance.

2. for this purpose i create a form . and create 2 Data Blocks
1. Non Database Data Block (layout type: form)
2. Database Data Block (layout type: Tabular)

3. in Non Database Data Block i add 3 Items.. on the basis of i get the records from database and show them into my into my 2nd Data Block...

4. my problem is this that how can i get records from database and show them into my forms and then save thats records into my table by using Database Datablock.

View 2 Replies View Related

SQL & PL/SQL :: Best Method To Populate Calendar Entries On A Table

Jun 2, 2011

I have a table :

Product
A
B
C
D

and I was wondering if there is a quick method of populating it with calendar data so it would look like the following:

Product Year Month
A 2008 Jan
A 2008 Feb
A 2008 Mar
A 2008 Apr
A 2008 May
A 2008 Jun
A 2008 Jul
A 2008 Aug
A 2008 Sep
A 2008 Oct
A 2008 Nov
A 2008 Dec
A 2009 Jan
A 2009 Feb
Etc.

This would be done for all products for 4 years.

View 2 Replies View Related

Forms :: How To Populate Multi-Record Block

May 3, 2011

i have two blocks called BLOCK1 AND BLOCK2

BLOCK1 is a multi record block with a field, FIELD_A
BLOCK2 is a multi record block with fields FIELD_B,FIELD_C,FIELD_D

i have a button on BLOCK1 called 'FETCH'.

The requirement is when_button_pressed on 'FETCH'. BLOCK2 must be populated for each record of BLOCK1. Population logic is based on, values from a database table TABLE_A which has got column values corresponding to all fields of BLOCK1 and BLOCK2.

View 7 Replies View Related

SQL & PL/SQL :: How To Have Procedure Populate Staging Table Primary Key

Jun 6, 2012

I've a staging table STG_TABLEA which has a primary key discount_seq_no.

I am creating a pl/sql procedure to populate a primary key (discount_seq_no) with a database sequence. The intent is to keep this populated with next value incrementing by 1.

I am using Oracle 11.2.0.2 version.

I've put together the below code, not sure on next steps...

BEGIN
UPDATE STG_TABLEA
SET A.DISCOUNT_SEQ_NO = "INSERT A SEQUENCE HERE AND KEEP INCREMENTING the seq value by 1
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved