TRG_ORDER

<DEFAULT SERVER>   >   <DEFAULT DATABASE>   >   <DEFAULT SCHEMA>   >   TRG_ORDER   

Simple Trigger of BEFORE "INSERT on sales_order_items"
Name Types Value
CUR Variable
Impact Analysis:
Object calling \"CUR\" Object Type
<DEFAULT SCHEMA>.TRG_ORDER Trigger
CUST_FNAME Variable
Impact Analysis:
Object calling \"CUST_FNAME\" Object Type
<DEFAULT SCHEMA>.TRG_ORDER.Select Select Statement
CUST_LNAME Variable
Impact Analysis:
Object calling \"CUST_LNAME\" Object Type
<DEFAULT SCHEMA>.TRG_ORDER.Select Select Statement
CUST_NAME Variable
Impact Analysis:
Object calling \"CUST_NAME\" Object Type
<DEFAULT SCHEMA>.TRG_ORDER Trigger
CUSTOMER_ID Variable
Impact Analysis:
Object calling \"CUSTOMER_ID\" Object Type
<DEFAULT SCHEMA>.TRG_ORDER.Select Select Statement
EXIST_PROD Variable
Impact Analysis:
Object calling \"EXIST_PROD\" Object Type
<DEFAULT SCHEMA>.TRG_ORDER Trigger
<DEFAULT SCHEMA>.TRG_ORDER.Select Select Statement
PROD_1 Variable
Impact Analysis:
Object calling \"PROD_1\" Object Type
<DEFAULT SCHEMA>.TRG_ORDER Trigger
PROD_2 Variable
Impact Analysis:
Object calling \"PROD_2\" Object Type
<DEFAULT SCHEMA>.TRG_ORDER Trigger
PROD_NAME Variable
Impact Analysis:
Object calling \"PROD_NAME\" Object Type
<DEFAULT SCHEMA>.TRG_ORDER Trigger
<DEFAULT SCHEMA>.TRG_ORDER.Select Select Statement
<DEFAULT SCHEMA>.TRG_ORDER.Select Select Statement
PROD_NUM Variable
Impact Analysis:
Object calling \"PROD_NUM\" Object Type
<DEFAULT SCHEMA>.TRG_ORDER.Select Select Statement
PROD_SIZE Variable
Impact Analysis:
Object calling \"PROD_SIZE\" Object Type
<DEFAULT SCHEMA>.TRG_ORDER.Select Select Statement
TEMP_1 Variable
Impact Analysis:
Object calling \"TEMP_1\" Object Type
<DEFAULT SCHEMA>.TRG_ORDER Trigger
TEMP_2 Variable
Impact Analysis:
Object calling \"TEMP_2\" Object Type
<DEFAULT SCHEMA>.TRG_ORDER Trigger
Name Definition
No Data 

CREATE OR REPLACE TRIGGER TRG_ORDER BEFORE INSERT on sales_order_items

DECLARE

    Exist_Prod BOOLEAN;
    Prod_name varchar2;
    type ProductsCur is REF CURSOR;
    Cur ProductsCur;
    temp_1 VARCHAR2(1000);
    temp_2 VARCHAR2(200);
    prod_1  VARCHAR2(80);
    prod_2  VARCHAR2(80);
    Customer_Id int;
    prod_size int;
    cust_fname VARCHAR2(80);
    cust_lname VARCHAR2(80);
    cust_name VARCHAR2(80);
    Prod_num Number;

begin

    --IF NOT EXISTS (Select * from product
    --                                Where id = :NEW.prod_id) Then
    Select DECODE(count(*),0,false,1,true) into Exist_Prod
    from product
    Where id = :NEW.prod_id;
    IF NOT Exist_Prod Then
        raise_application_error(-25114, 'The product does not exist');
    End If;
    
    Select count(*) into Prod_num
    from product
    Where id = :NEW.prod_id;
    
    Select name into Prod_name
    From product
    Where id = :NEW.prod_id;
    
    If prod_name = '' Then 
        raise_application_error(-25114, 'The product is not valid');
    End If;
 

    IF Prod_name > 0 
    THEN
        select fname, lname, name, prod_size
            into cust_fname, cust_lname, prod_name, prod_size
            from all_customers, all_products
        where all_customers.id = Customer_Id ;

    open cur for Prod_name using temp_1, temp_2;
    loop
        fetch cur into prod_1, prod_2;
        exit when cur%NOTFOUND;
        if instr(prod_1, '.') = 0
        then prod_1 := temp_1 || '.' || cust_name;
        end if;

        If temp_1 is null Or temp_1 = '' Then
            CUSTOMERS.GetFullname(temp_1, temp_2);
             sp_deleteemployee(temp_1);
        end if;
    end loop;
    End If;

end

     
Name Types Description
<DEFAULT SCHEMA>.CUSTOMERS Package Package, Interface
<DEFAULT SCHEMA>.PRODUCT Table Table
<DEFAULT SCHEMA>.SALES_ORDER_ITEMS Table Table
<DEFAULT SCHEMA>.ALL_CUSTOMERS View View
<DEFAULT SCHEMA>.ALL_PRODUCTS View View
<DEFAULT SCHEMA>.SP_DELETEEMPLOYEE Procedure Procedure body SP_DELETEEMPLOYEE(EMPLOYEEID NUMBER)
COUNT Function PL/SQL System Function
DECODE Function PL/SQL System Function
<DEFAULT SCHEMA>.CUSTOMERS.GetFullName Function Function interface GetFullName(fname NVARCHAR2,lname NVARCHAR2) return NVARCHAR, Package CUSTOMERS
INSTR Function PL/SQL System Function
DBMS_STANDARD.RAISE_APPLICATION_ERROR Function PL/SQL System Function
FNAME Column Virtual column
<DEFAULT SCHEMA>.ALL_CUSTOMERS.ID Column View column
<DEFAULT SCHEMA>.ALL_CUSTOMERS.LNAME Column View column
<DEFAULT SCHEMA>.ALL_PRODUCTS.PROD_SIZE Column View column
BOOLEAN PL/SQL System type PL/SQL System Object
INT PL/SQL System type PL/SQL System Object
NUMBER PL/SQL System type PL/SQL System Object
REF CURSOR PL/SQL System type PL/SQL System Object
VARCHAR2 PL/SQL System type PL/SQL System Object
FALSE Undefined Item
NAME Undefined Item
TRUE Undefined Item
<DEFAULT SCHEMA>.PRODUCT.ID Primary Key Column
<DEFAULT SCHEMA>.SALES_ORDER_ITEMS.PROD_ID Foreign Key Column

     
No Data