logo
down
shadow

Oracle Trigger BEFORE INSERT has No data found


Oracle Trigger BEFORE INSERT has No data found

Content Index :

Oracle Trigger BEFORE INSERT has No data found
Tag : sql , By : vitorcoliveira
Date : November 23 2020, 03:01 PM

I think the issue was by ths following , I am trying to make a trigger that will prevent you from inserting a new Bill Item into the "Bill_Item" table if the Bill has been paid. The Bill Table has a Paid_YN and a Posted_YN column within. So depending on which bill you will be adding to, it will deny it if it has been paid or posted. My update and delete sections work within this code, but not the "Inserting" section. Please help! My Apologies for lack of indentation, Could not get the code to copy in properly. please let me know where I am going wrong! is it my logic? I have tried googling and searching here for the error messages, but still cannot figure it out! , Here is your bloomer:
  ELSIF INSERTING THEN
    SELECT Paid_YN, Posted_YN
    INTO v_paid_YN, v_Posted_Yn
    FROM Bill
    WHERE Bill_Number = :OLD.Bill_Number;
  ELSIF INSERTING THEN
    SELECT Paid_YN, Posted_YN
    INTO v_paid_YN, v_Posted_Yn
    FROM Bill
    WHERE Bill_Number = :NEW.Bill_Number;

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

ORACLE SQL Trigger - no data found


Tag : sql , By : Antony Briggs
Date : March 29 2020, 07:55 AM
Does that help That error occurs when you execute a select..into statement that doesn't return a row, for instance the first one in your trigger.
SELECT NAME INTO PASSENGER_NAME FROM PASSENGER JOIN BOOKING
ON PASSENGER.PASSENGER_ID = BOOKING.PASSENGER_PASSENGER_ID
WHERE BOOKING.PASSENGER_PASSENGER_ID = :NEW.PASSENGER_PASSENGER_ID
AND ROWNUM  = 1;
SELECT NAME INTO PASSENGER_NAME 
FROM PASSENGER 
WHERE PASSENGER.PASSENGER_ID = :NEW.PASSENGER_PASSENGER_ID;

Trigger in Oracle: No data found


Tag : oracle , By : beebob
Date : March 29 2020, 07:55 AM
I wish did fix the issue. Using pragma autonomous_transaction means that the trigger cannot see the row that has just been inserted and which cause it to be fired.
When you removed the primary key and pre-inserted a record with the same ID, it's that row that will be updated by your trigger, not the new one you are inserting - so it still won't do what you want, and duplicating the PK value isn't sensible anyway.
create or replace trigger tvideo_2 after insert on video
begin
  update video
  set age = 18
  where title like '%18+%'
  and age != 18;
end;
/
create or replace trigger tvideo_2 after insert on video
begin
  for rec in (
    select videoid, title
    from video
    where title like '%18+%'
    and age != 18
  )
  loop
    update video
    set age = 18
    where video.videoid = rec.videoid;
    dbms_output.put_line('Video '||rec.videoid||' has been updated age to 18+');
  end loop;
end;
/
create or replace trigger tvideo_2 before insert on video for each row
begin
  if :new.title like '%18+%' then
    :new.age := 18;
    dbms_output.put_line('Video '||:new.videoid||' has been updated age to 18+');
  else
    dbms_output.put_line('Video '||:new.videoid||' is not 18+!');
  end if;
end;
/

trigger in oracle allows insert if field is found


Tag : oracle , By : goffi
Date : March 29 2020, 07:55 AM
wish helps you I have three tables called products, customers and reserve_products. The trigger is on the reserve products table and will only let you insert into the table if the customer id and product id and quantity of product is found and not 0. I am having problems with the sql and checking if the rows exist.
CREATE OR REPLACE TRIGGER trg_reserve_products
BEFORE INSERT ON reserve_products
FOR EACH ROW
DECLARE
  v_count NUMBER;
  v_p_id NUMBER(6);
  v_c_id NUMBER(6);
  v_p_quantity products.p_quantity%TYPE;
  v_p_name products.p_name%TYPE;
BEGIN
  SELECT COUNT(*)
  INTO v_count
  FROM products
  WHERE p_id = :NEW.p_id;
  IF v_count = 0 THEN
    raise_application_error(-20000 , 'Invalid Product ID.');
  END IF;

  SELECT COUNT(*)
  INTO v_count
  FROM customers
  WHERE c_id = :NEW.c_id;
  IF v_count IS NULL THEN
    raise_application_error(-20000 , 'Invalid Customer ID.');
  END IF; 
  
  SELECT p_quantity, p_name
  INTO v_p_quantity, v_p_name
  FROM products
  WHERE p_id = :NEW.p_id;  
  IF v_p_quantity < 1 THEN
    raise_application_error(-20000 , v_p_name || ' 4 is currently out of stock.');
  END IF; 
  
  UPDATE products
  SET p_quantity = p_quantity - 1
  WHERE p_id = :NEW.p_id;
END trg_reserve_products;

SELECT * FROM products;

INSERT INTO reserve_products VALUES(1, 101, 1);
INSERT INTO reserve_products VALUES(2, 46, 1);
INSERT INTO reserve_products VALUES(3, 46, 2);
INSERT INTO reserve_products VALUES(4, 46, 3);

SELECT * FROM products;

SELECT * FROM reserve_products;

Oracle trigger on 2 tables : no data found


Tag : oracle , By : stu73
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , Please help,I'm trying to allow/ban insertion into a table called 'vol' that has a foreign key (id_av) from another table 'avion' , Perhaps something like this?
create or replace trigger t
  before insert on vol 
  for each row          --> edited
declare 
  etat VARCHAR(10);
BEGIN 
  -- MAX will prevent NO-DATA-FOUND 
  -- Also, you don't need join - use :NEW.ID_AV which is equal to currently inserted value
  select max(avion.etat)
  into etat 
  from avion
  where avion.id_av = :new.id_av;

  -- NVL because - if SELECT returns, nothing, you can't compare NULL with 'disponible'
  IF nvl(etat, 'x') <> 'disponible'
  THEN 
    RAISE_APPLICATION_ERROR( -20001, 'insertion imposible');
  END IF;
END t;
/ 

How to change old data of table with before insert trigger in oracle?


Tag : oracle , By : Andrew Mattie
Date : March 29 2020, 07:55 AM
This might help you You do not need to use PRAGMA AUTONOMOUS_TRANSACTION, and do not use COMMIT inline inside your procedures.
For your case it's nice to CREATE TABLE test with ID column defined as number generated always as identity primary key.
 SQL> CREATE TABLE test(
                  id       number generated always as identity primary key,
                  item     varchar2(100),
                  quantity int
 );
 /
 SQL> INSERT INTO test(item,quantity) VALUES ('KA1',5);
 SQL> INSERT INTO test(item,quantity) VALUES ('KA2',2);

 SQL> CREATE OR REPLACE PROCEDURE XXI_MULTI_PR_REMOVE( I_ITEM varchar2 ) IS
 BEGIN
  DELETE TEST WHERE ITEM = I_ITEM;
 END;
 /
 SQL> CREATE OR REPLACE TRIGGER MERG_DUP
 BEFORE INSERT ON TEST
 FOR EACH ROW
 DECLARE  
      v_qty NUMBER;
 BEGIN
    BEGIN
      SELECT SUM(NVL(QUANTITY,0)) INTO v_qty FROM TEST WHERE ITEM = :NEW.ITEM;
     EXCEPTION WHEN OTHERS THEN v_qty := NULL; 
    END;  
    IF ( v_qty IS NOT NULL ) THEN
        XXI_MULTI_PR_REMOVE(:NEW.ITEM);
       :NEW.QUANTITY:=:NEW.QUANTITY+v_qty;    
    END IF;
 END MERG_DUP;
 /
 SQL> INSERT INTO test(item,quantity) VALUES ('KA3',6);
 SQL> COMMIT;
 SQL> SELECT * FROM test;
Related Posts Related QUESTIONS :
  • How to create new column with values counting up every 9th value with SQL?
  • Most frequent combination of words/characters SQL Server
  • Why can't I access a field defined as "Select 1" from a subquery in the outer query?
  • How to solve Msg 8115, Level 16, State 2, Line 2 Arithmetic overflow error converting expression to data type int.?
  • UPDATE row inserted earlier in CTE
  • Display current month values based upon abbreviation (Oct) SQL Server
  • SQL Modify returned select value without union
  • How to put a variable in a like statement in vba?
  • More Elegant way to Batch Validate Phone Numbers
  • SQL find parent where children match over multiple rows
  • A combo box that has to be filter the same data in that field
  • Exclude updated record from trigger function
  • Insert into table from select only when select returns valid rows
  • How to fix SQLite3::SQLException: near "to": syntax error: SELECT "".* FROM "" WHERE "
  • Filter msdb.dbo.sysjobsteps.command into seperate columns
  • How to extract year or month,day from oracle function without using sub-query?
  • Reset sum when condition is met in Oracle
  • Date Between (Start & Now)
  • Reset running sum when condition is met in Oracle
  • Why is my date criteria filtering incorrectly in a MsysObjects query using DCount?
  • Stripping date/time delimiters in an SSIS csv export
  • SSIS Error on insertion - Error when new primary keys in Dimension table
  • How can I make a temp table of specific datetime data within a range?
  • AnalysisException: Syntax error in SQL After multiple join
  • SQL Query Multiple Columns Using Distinct on One Column Only and Using Order By
  • Sum inventory item groups
  • TSQL: Parsing strings with various characters
  • Using SQL Server : how to use select criteria based on sum
  • Inserting dynamic pivot Result into Temp table
  • Joining multiple fields in a dataset joined with descriptions
  • Conditional WHERE EXISTS in Oracle query
  • How to get the count of each unique value in a column between two dates?
  • SQL: Select only if multiple values are IN 1 column from CatalogTable
  • In a query (no editing of tables) how do I join data without any similarities?
  • Create nested json blobs in PostgreSQL
  • Getting extra dates while using cte
  • Way to alter one table if another was altered
  • Proper use of semicolons in functions and procedures
  • Cursor Not looping all the Records
  • Select inner join returns wrong result
  • Group By showing duplicates
  • Error in using subquery in access join query
  • How do I use a composite pk in a stored procedure?
  • Oracle Unique Indexes
  • Spooling data to CSV truncates
  • How do views reduce code duplication?
  • SQL: Gather right hand values from a join
  • SQL Agent Logon - What is going on?
  • How do I combine two rows of same part, but add quantities?
  • How to optimize this SQL query for a rectangular region?
  • Pass data from ssrs subreport to parent
  • how to enter values in rowguid column?
  • Read multiple tables from dataset in Powershell
  • In SQL how to I exclude a record if there are more than 3 characters after a dash
  • What is the difference between check and foreign key?
  • How to return a record from function, executed by INSERT/UPDATE rule (trigger)?
  • SQL order by oldest from Unix Timestamps
  • How to convert decode function in oracle to standard big query
  • Postgresql: An alternative to subqueries to make the query more efficient?
  • Use returned values from a query in the same procedure
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com