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
ADVERTISEMENT
Sep 6, 2012
how can i put my query inside my main query.
select *
from (select pa_request_id
,max(status) status
,max(approved_amount) approved_amount
,min(level_id) level_id
,max(req_amount) req_amount
from target_aggregation_attendee
group by pa_request_id)
where status = 'Approved')
My Main Query:
select
TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername,
TARGET_EMPLOYEE.GE_ID as requesterGEID,
TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName,
TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg,
TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg,
TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
[Code]....
View 4 Replies
View Related
Jun 6, 2010
In a trigger(on update of a table t1) I am trying to write, I am doing an insert on t2 accessing ':new' values of the update on t1.
But in my Insert statement, I am having get one of the column values from another table. How can I write my insert statement in such a way as to insert values contained in ':new' pseudo columns and a select from another table. Below is my insert statement in the trigger :
-------
IF (:old.GROUP_YELLOW <> :new.GROUP_YELLOW) THEN
INSERT INTO TEST.W_THRESHOLD_LOG
(THRESHOLD_LOG_WID, CHANGE_DATE, MEASURE_TYPE_WID, MEASURE_NAME, CUSTOMER_WID, CUSTOMER_NAME, USER_ID, CHANGED_ITEM, PREV_VALUE, NEW_VALUE)
VALUES(TEST.W_THRESHOLD_LOG_SEQ.NEXTVAL, SYSDATE, :new.MEASURE_TYPE_WID, 'Rolling Stabilty' , :new.CUSTOMER_WID, 'Customer1', 'User1', 'GROUP_YELLOW', :old.GROUP_YELLOW , :new.GROUP_YELLOW);
END IF;
-------
In the above code if the hardcoded value 'Customer1' need to be picked from another table,
i.e .
SELECT NAME FROM W_CUSTOMER_DIM WHERE CUSTOMER_WID = THRESHOLD.CUSTOMER_WID
how can I rewrite my query to the above value from the select into my insert statement..?
View 24 Replies
View Related
Jun 5, 2010
I read in a book that you can't use subquery in an insert statement . E.g:
1)insert into dates (date_col) values (select sysdate fom dual) but when i tried using subquery like this:
2)insert into regions values ((select max(region_id)+1 from regions), 'Oce');
This query worked but 1st query didnt.From my assumptions if we try inserting values in table with the subqueries for a particular column as in 1st query , it will throw error but not while inserting values in all columns as in 2nd query.
View 5 Replies
View Related
Sep 5, 2012
I have an athletics participation table that only has the relevant emplid and effective date field. There is no term field on the table. I'm trying to only select those emplid's where the max( effdt) for the emplid is between the begin and end date of the current term. I only want to select current athletes. I would much rather it be for the current academic year but it seems impossible. Why can't I use max(effdt) here?
sql Code
AND h.emplid IN(SELECT b.emplid FROM PS_ATHL_PART_STAT b
where max(b.effdt) between (SELECT term_begin_date AND term_end_date
from PS_TERM_TBL
[Code].....
View 2 Replies
View Related
May 17, 2010
select * from course_section
where term_id = (select term_id from term where term_desc in ('Spring 2006','Spring 2007'));
and it is showing:
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
View 5 Replies
View Related
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
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
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
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
View Related
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
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
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
Jun 11, 2010
I have 3 tables, user_login_event, person and resource_viewed_event. What I want to do have a report for each month, users logged in our application and then show for each month, how many records were created in table person and how many resource views events were logged in resource_viewed_event.
Lets only worry about the timestamp fields in these tables now as I want to use them to join the tables together or at least build correlated subqueries along the months. I have tried several options, all not leading to a desired result:
Left outer join. Works but its incredibly slow:
SELECT
distinct to_char(ule.TIMESTAMP,'YYYY-MM') as "YYYY-MM",
count(distinct ule.id) as "User Logins",
count(distinct ule.user_id) as "Users logged on",
count(distinct p2.id) as "Existing Users",
count(distinct p1.id) as "New Users",
count(distinct r1.id) as "Resources created"
[code]....
Tried the same with left outer joins of temporary tables created through select statements:
select
distinct ule.month as "Month",
count(distinct p1.user_id) as "Users created",
count (ule.id) as "Logins",
count (distinct ule.user_id) as "Users logged in",
count(rv.id) as "Resource Views",
count(distinct rv.resource_id) as "Resources Viewed"
[code]....
Tried the same with left outer joins of temporary tables created through select statements:
select
distinct ule.month as "Month",
count(distinct p1.user_id) as "Users created",
count (ule.id) as "Logins",
count (distinct ule.user_id) as "Users logged in",
count(rv.id) as "Resource Views",
count(distinct rv.resource_id) as "Resources Viewed"
[code]....
another approach is to create my own temporary tables using select statements and create fixed Month values which I can use to directly link the sets together.
select
distinct ule.loginday as "Month",
count(distinct ule.id) as "Logins",
count(distinct ule.user_id) as "Users logged in",
count(distinct p1.user_id) as "Users created",
count(distinct p2.user_id) as "Existing users1"
[code]....
performance is OK with 2 tables but the example above takes forever to execute.
Tried an approach with union but this creates new rows for each table
SELECT DISTINCT p1.MONTH AS "Month",
COUNT(DISTINCT p1.user_id) AS "Users created",
NULL AS "Logins",
NULL AS "Users Logged in",
NULL AS "Resource views",
NULL AS "Resources viewed"
FROM (SELECT To_char(person.created_on_date, 'YYYY-MM') AS MONTH,
[code]....
View 4 Replies
View Related
May 14, 2011
While writing a procedure I went into this problem. Whenever I write Query : Select * from dba_pending_transactions It works fine.
But whenever I use same Select Query inside PL-SQL block it gives error Table or view not exist. Dba_pending_transactions is view.
SQL> declare
2 v_count number(2);
3 begin
4 execute immediate 'select count(*) from dba_ending_transactions' into v_count;
5 dbms_output.put_line(v_count);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 4
Same error I get when i use it inside a procedure.
View 2 Replies
View Related
Nov 25, 2012
can we place insert statement in loop inside anonymous block?
CREATE TABLE DEP(DEPTID NUMBER(5) NOT NULL PRIMARY KEY,DNAME VARCHAR2(10),LOCID VARCHAR2(10));
DECLARE
I NUMBER(5);
BEGIN
I := 0;
LOOP
INSERT INTO DEP VALUES(&DEPTID,'&DNAME',&LOCID);
I := I+1;
EXIT WHEN I = 5;
END LOOP;
END;
View 6 Replies
View Related
May 1, 2011
I have a problem executing a function.
There are two tables,
1. Table with column names of a second table mapped to certain variables.
ex: Table1
col1, var1
col2, var2
col3, var3
--------
--------
2. Table with the values for columns as given in table 1 ex:
col1, col2, col3, col4, col5,
a , aa , 1 , x1 , p
b , ab , 2 , x2 , q
c , ac , 3 , x3 , r
I have to select values from table2.col1 do some processing and calculate values and store it in a table then do the same thing with col2 and so. This needs to be done for all the columns that appear in table1.For example in table 1 i have only three columns mentioned thus i have to process col1, col2 and col3 from table2. col4 and col5 will not be processed since they do not appear in the first table.
The problem is i have hundred columns in table 2 and the user can add up to hundred columns in table 1 as and when it is required.
I have created a cursor to first select column name from table 1 where variable is not null.For each value in cursor i put it in a local variable.
Second step is to select values from table2 where instead of column name i am using the local variable.But the problem is instead of choosing values from col1 the query returns the value as col1 (the value of local variable)
View 2 Replies
View Related
Oct 21, 2012
I have 2 tables, ASSIGNMENT and RESEARCH_PAPER. For each research paper, I need to find out :
1. The number of assignments created from it (after a given constant assign date)
2. The number of assignments created from it that have been approved.
3. The number of unique users who have either created or approved an assignment from it
Test data :
create table research_paper (id int, name varchar2(100));
create table assignment (id int, r_paper_id int, assigner_id int, assignee_id int,
approver_id int, assign_date timestamp, approved_yn varchar2(10));
insert into research_paper values (1, 'A');
insert into research_paper values (2, 'B');
[code]....
Assignment :
id r_paper_id assigner_id assignee_id approver_id assign_date approved_yn
-----------------------------------------------------------------------------------------------------------
11 100 200 100 23-10-12 12:00:00.000000000 AMY
22 200 100 200 22-10-12 12:00:00.000000000 AMN
32 100 200 101 24-10-12 12:00:00.000000000 AMY
[code]....
Research_paper:
id name
----------
1A
2B
Expected result :
r_paper_id created approved unique_users
-----------------------------------------------
1 3 2 4
2 3 2 3
I wrote the following query for that :
SELECT rp.id r_paper_id,
COUNT(*) created,
COUNT(
CASE
WHEN a.approved_yn = 'Y'
[code]....
But it fails, saying that 'single-row subquery returns more than one row' when I introduce the 'unique_users' clause. The remaining fields of the output are correct.
View 7 Replies
View Related
May 7, 2013
In 11g, When I am trying to insert the records with select insert option it is failing.
Below is my Query:
insert into table_1 select * from table_2
The above query is not inserting any records into table_1. But when i query select * from table it is returning records.
The same select Insert query is inserting records properly in 10g.
View 5 Replies
View Related
Jan 12, 2011
If i inserted the values in table it gets inserting very few rows only.I dont know y it is?
View 15 Replies
View Related
Nov 12, 2006
I have a table known as Registration in which there are columns StudentID, ModuleId, year, semester
semester = 1 or 2
I want to write a trigger so that student can register on a maximum of 4 modules per semester
i.e.
select count(*), semester from Registration where studentid='241234' group by semester
I tried the following trigger but I am stuck
If I dont use "for each row", I am not allowed to use :new and ld and when I use "for each row", count would be for each row which would be 1 or 0
create or replace trigger checkmodulecount
before insert on Registration
DECLARE
noCourse NUMBER;
nCount NUMBER;
FAILED EXCEPTION;
[Code]....
WHEN FAILED THEN
raise_application_error(-20000,'Cannot reigster for more than four courses');
END;
View 1 Replies
View Related
May 7, 2010
I have to write a trigger where when the table is updated then one column named 'Status' should be updated as 'U' and if arow is inserted in the table then the column 'Status' needs to be inserted with value 'I'.
Create table test_trig
(
vempno number,
vempname varchar2(20),
status char
)
New to triggers....how to go with both insert and update conditions together.
Can we write a trigger which takes care of both insert and update. I have used Merge statement where I can write conditions based on insert/update done.
View 4 Replies
View Related
May 24, 2011
In
CREATE table ALPHA (
ID NUMBER,
FK_NR NUMBER,
INPUT NUMBER,
OUTPUT NUMBER,
MY_COUNT NUMBER,
ACTUAL_TOTAL NUMBER
);
I would like to record new inputs or outputs andthe column MY_COUNT should display the actual count of records for INPUT (actually MOD(INPUT, 10) ) regarding FK_NR and ACTUAL_TOTAL should display the actual sum for all input/output for FK_NR.
--ID normally comes from a sequence and BEFORE INSERT TRIGGER
EXAMPLE
INSERT INTO ALPHA(FK_NR, INPUT, OUTPUT)
VALUES (1 ,10,NULL);
INSERT INTO ALPHA(FK_NR, INPUT, OUTPUT)
VALUES (1 ,10, 50);
[code]...
Table ALPHA looks like:
1,1,10,NULL,1,10
2,1,10,50,2,-30
3,2,NULL,20,0,-20
4,2,20,40,2,-40
5,3,40,20,4,20
6,3,-10,NULL,3,10
How can this be achieved ?I started with a
CREATE OR REPLACE
TRIGGER "BI_ALPHA"
BEFORE INSERT ON ALPHA
FOR EACH ROW
BEGIN
SELECT ALPHA_SEQ.nextval INTO :NEW.ID FROM DUAL;
END;
and thought of a
CREATE OR REPLACE TRIGGER "AI_ALPHA"
AFTER INSERT ON "ALPHA"
FOR EACH ROW
DECLARE
l_maxidNUMBER;
l_fknr NUMBER;
[code]...
But by inserting a row, this results in ORA-04091
View 6 Replies
View Related
May 2, 2013
I must to build triggers that insert other two rows when the user insert the first record, but the First record must to change Seq to 2 (was 1) then in trigger to insert other record with seq equal 1 and more other record with seq equal 800, I tried some ways , but return error
First insert the user from application
INSERT INTO ARCTB_ACAO_IMEDIATA ( CD_ARC, CD_TIPO_ACAO, CD_ACAO_IMEDIATA, DS_ACAO, CD_RESPONSAVEL, DT_ORIGINAL, DT_ABERTURA, NR_PRAZO,DT_PREVISTA, DS_HISTORICO ) VALUES (9732, 0,1, 'TESTE','ucrwilma', To_date('02/05/2013','DD/MM/YYYY'), To_date('02/05/2013','DD/MM/YYYY'), 1, To_date('02/05/2013','DD/MM/YYYY'), '');I create triggers
CREATE OR REPLACE TRIGGER ARCTR_ACAO_IMEDIATA_I
BEFORE INSERT
[Code]....
END ARCTR_ACAO_IMEDIATA_IUreturn me error
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "CLIBGF.ARCTR_ACAO_IMEDIATA_IU", line 221
ORA-04088: error during execution of trigger 'CLIBGF.ARCTR_ACAO_IMEDIATA_IU'
ORA-06512: at line 7using 9.2.08
View 2 Replies
View Related
Jun 13, 2012
I'm trying to insert data in my_second_table using a trigger and a view when I insert the data in my_first_table but there's something wrong in the code.
CREATE OR REPLACE TRIGGER my_trigger
AFTER INSERT ON my_first_table
FOR EACH ROW
BEGIN
[Code]...
I'm suspecting that the problem is in the :new.cod_emp
P.S.: I'm using: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production on Enterprise Linux Server release 5.5 (Carthage)
View 15 Replies
View Related
Jun 12, 2013
I have make a new trigger.Create a trigger that inserting a new job_id MAX_SALARY assigned as the employee's salary more than 80 departmental charges
I have that code, is that correct?
CREATE OR REPLACE
TRIGGER TR27
AFTER INSERT ON JOBS FOR EACH ROW
BEGIN
(SELECT MAX(SALARY) FROM EMPLOYEES WHERE DEPARMENT_ID=80);
:NEW.MAX_SALARY := :OLD.MAX_SALARY;
END;
What I need to complete it?
View 2 Replies
View Related
Aug 17, 2010
writing a trigger body. My requirement is i need to insert a new record in a task table when ever a new record is inserted into employee table.Here in the trigger i need to select the name of the employee in the last inserted row in employee table and insert the name in task table.I tried to write the code as below
insert into task(name, date, type) values ((select name from employee where emp_id=(select max(emp_id) from employee), sysdate, 'document'));
When i am trying to insert record using trigger, it is taking last but one record from the employee table.
View 2 Replies
View Related
Jun 2, 2011
Select * from Table A where emp=1;
Emp AID
1 111
select * from Table B where emp=1 ;
----------
AID, emp, start_dt, End_dt
111 1 01-jan-2011 31-dec-2011
112 1 01-jan-2011 31-dec-2011
113 1 01-jan-2011 31-dec-2011
I have After insert or update trigger on Table B. This will update AID column on Table B.
after insert update on table B
update A
set AID=:new.AID where emp=:new.emp;
if i end date any record on table B i wanted to update max(AID) on Table A.
EX update table b
set end_dt= sysdate-1
where aid=111;
So I wrote the trigger on Table A as below
before insert or update on trigger A
declare
v_aid number;
v_strt_dt date;
v_end_dt date;
begin
select max(AID) from table B
where emp=:new.emp;
select v_end_dt from table B where aid=:new.Aid;
if v_end_dt<sysdate then
:new.aid:=v_AID;
end if;
But the problem here is its giving mutating error.Then i tried with autonomus transaction but it willreturn old value when it fiers.
So how can i achive both the task at a time.That means i have to endate Table A , Same time i have to update active max(AID) to table B.
View 13 Replies
View Related
Feb 1, 2011
I am calling an after insert Trigger on table1.
In the trigger I am calling a procedure that returns an error if there is any error returned from procedure. I have to update the table table1's column error_desc (for the same new inserted record for which the trigger was called) with the error received by OUT parameter of procedure called in trigger. I have to update the same record on whose insert this trigger was called.
View 3 Replies
View Related
Oct 30, 2006
I must create an INSERT trigger, on an Oracle table, which will do an insert into my MS-SQL 2000 DB table.
The tables are exactly the same in this case and I desire to insert the entire row that was just insterted into the Oracle table into the MS-SQL table.
I understand how to create an ODBC connection between the DB servers, I just can't seem to understand the trigger syntax.
View 2 Replies
View Related
