Validation In Database Through Statement Level Trigger

Mar 23, 2011

I have a table which contains data of all calcualted payements for a Land for a particular period i.e. for a Land 124 for the duration of Jan-2010 to Mar-2010 100 rupees rent is paid to the owner.Requirement here is that for a particular duration i.e is for Jan-2010 to Mar-2010 system calculation of rent for a land file multiple times where the table would contain history of each transaction i.e. a user can calculate for a Land 124 for duration fo Jan-2010 to Mar-2010 rent more than ten times and there would be ten records in the table but only one record out of these would be valid where the user cannot calcualte rent for Lnd 124 for Jan-2010 to Mar-2010 without updating the status of the last record in the table for the same land and duration to invlaid. I want to add this business validation through triggers as i am not able to do so through check constarinst.I want to ensure that only one record should be valid for a particular land for a particular duration since row level triggers do not allow us to query the table ion which the trigger is executing i am trying to use statement level trigger but i have a few doubts here: Incase of a Before Insert statement level trigger does the trigger have access to the data which is being inserted into that the table cause whenever a record is being inserted into a table the systen would check through the trigger that if there is a valid (payment_status is valid) payment record in the table for that same land file and same duration.If there is a valid record then the trigger would through an exception. I know you can use a row level trigger with an autonomous transaction should work wihtout giving the mutating table error issue but i wont to know if using a statement level trigger here is feasible!!

View 11 Replies


ADVERTISEMENT

SQL & PL/SQL :: Statement Level Trigger To Get Rows?

Jun 20, 2012

I have written trigger to satisfy the requirement, when i write row level it comes out with mutating error on table which is very obvious, i have change it to statement level which is fine but it doesn't satisfy the requirement.

I want to capture the id column , which rows got inserted or updated .

Is there any way to get the newly inserted or updated rows as in case of row level i can get it form :new.id.

View 9 Replies View Related

PL/SQL :: Possible To Add Condition In Statement Level Trigger

Jul 17, 2012

is it possible to add condition in statement level trigger

CREATE OR REPLACE TRIGGER insert_trigger after
INSERT ON Table when condition--- or any condition like.

how we use in row level trigger

View 2 Replies View Related

SQL & PL/SQL :: Difference Between After / Before Insert In Row Or Statement Level Trigger

Feb 8, 2012

what is the difference between after or before insert in row level trigger and statement level trigger.

View 3 Replies View Related

SQL & PL/SQL :: Created Trigger On Database Level In System Schema

Jun 10, 2010

I have created trigger on database level in system schema. While i am creating new tables in system schema, trigger logged the entry but when i am creating table in scott schema it is not working for that.

CREATE OR REPLACE TRIGGER ddltrigger
AFTER DDL ON DATABASE
BEGIN
INSERT INTO aud_log
(user_name, ddl_date, ddl_type, object_type,
owner, object_name
)
VALUES (ora_login_user, SYSDATE, ora_sysevent, ora_dict_obj_type,
ora_dict_obj_owner, ora_dict_obj_name
);
END;

View 3 Replies View Related

Forms :: Date Validation In PRE-INSERT Trigger?

Feb 1, 2013

In the attached PRE-INSERT Trigger Code i need to do a validation. Validatation: If the Date range is between FEB 6 2013 and MARCH 1 2013 then the code should work ELSE It should throw a message

Check_Product_Title;
Begin
if :prod2.internet_product_flag = 'Y' and :prod2.brnd_code is null then
soft_messages('E',TRUE,'Brand code must be specified for CCH online product');
end if;

[Code]....

View 3 Replies View Related

Forms :: Calling Validation In Pre-insert Trigger

Nov 10, 2010

my purpose is when PRE-INSERT trigger fires validation should be done like date format , primary key in table if validation is ok then a value of text box should be set to sequence no . else it should generate message

View 9 Replies View Related

PL/SQL :: ORA-04098 Trigger BANKTRAN_BEF_DEL Is Invalid And Failed Re-validation?

Jan 7, 2013

I created follwoing trigger successfully...

create or replace trigger BANKTRAN_BEF_DEL
before delete on BANKTRAN
declare
weekend_error EXCEPTION;
not_authentocated_user EXCEPTION;
begin

[code]...

but when deleting the records using query delete from BANKTRAN getting the below error

ORA-04098 trigger"BANKTRAN_BEF_DEL" is invalid and and failed re-validation

View 18 Replies View Related

SQL & PL/SQL :: Row Level And Statement Level

Apr 11, 2013

between statement level or row level trigger, which trigger will execute first.We have BEFORE_UPDATE_ROWLEVEL_TRIGGER and BEFORE_ UPDATE_ STATEMENT LEVEL_TRIGGER triggers on table product.

which will execute first on update DML event ?

View 2 Replies View Related

SQL & PL/SQL :: Create A Row-level Trigger Named TRG_PROB_FEE?

Dec 3, 2010

I'm trying to create a row-level trigger named TRG_PROB_FEE to run before insert or update of the problem fee attribute on the problem table. If the associated DML command would result in a null for the problem fee(prob_fee), then get the value of the problem fee attribute from the associated contract (con_probfee) to use as the value for problem fee (prob_fee) in the Problem table.

CREATE OR REPLACE TRIGGER TRG_PROB_FEE
BEFORE INSERT OR UPDATE OF PROB_FEE ON PROBLEM
FOR EACH ROW
BEGIN
IF :NEW.PROB_FEE IS NULL THEN
SELECT CON_PROBFEE
INTO PROBLEM
FROM CONTRACT
WHERE CON_PROBFEE = :NEW.PROB_FEE
END IF;
END;
/

View 8 Replies View Related

RMAN Production To Validation Database Refresh Until SCN

Nov 13, 2011

I am trying to refresh the validation database with the old production backup i.e. our requirement. I have given the rman script i have executed and the output error message amd the rman configuration setting. Plus in the next post ill post the current sucessfully running RMAN script for your reference.

rman TARGET sys/passwd@Production CATALOG rman_pmxp/rman@catlog AUXILIARY sys/passwd @validation
RMAN> show all
2> ;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 90 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'H:\backups\DB_Sunday_Backup\Prod_%d_%F.rman.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F';
[code]...

View 1 Replies View Related

Forms :: How To Read Values In A Multi Record Control Block Using Pre-insert Trigger (block Level)

Jul 24, 2010

I have a multi record control block (basically a text item displaying 6 records) where user enters values and I want to process the values using pre-insert trigger.

I want to read value in each record and then do some tasks using a pre-insert trigger before I commit the values. To navigate between the records I was using first_record, next_record, clear_record built-ins but it gives errors like "40737-illegalrestricted procedure next_record in pre-insert trigger".

View 3 Replies View Related

Creating A Trigger Within If-else Statement

May 25, 2012

I would like to create a trigger on a table, but only if the table exists.

This is what i've got so far:

create or replace function tableExists (tablename in varchar2) return boolean
is
c int; 
begin    

[Code].....

This would give me the error:

PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update

View 1 Replies View Related

SQL & PL/SQL :: Adding IF Statement To A Trigger

Jan 8, 2013

I have created the below trigger in Oracle to ensure that a value in a table remains at 180 but I need to restrict this to one row in this table. Is there a way to use an IF statement or a where clause of some type for a particular row in that table? The Row is called ADMIN. Triggers weren't covered in my course so only know a few basic statements.

CREATE OR REPLACE
TRIGGER DAYSAGO_trg
BEFORE UPDATE ON days_ago
FOR EACH ROW
BEGIN
:NEW.days_ago := 180;
END;

View 4 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 Statement In Trigger Accessing And A Select From A Table

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

Enable Audit On Database Level?

Aug 27, 2013

1.) Whatever statics/data should we gather to compare the database performance before and after enable the audit on database level?

2.) How can we enable audit on database level?

3.)what is pros and crons of enable audit database level?

View 1 Replies View Related

SQL & PL/SQL :: User Restriction On Database Level?

Dec 13, 2011

For the list of userid's, how to find the list of OS/Restricted Shell ID's at the database level ?

View 7 Replies View Related

Choose Right Raid Level For Oracle Database

Aug 10, 2010

Tell me the right raid level for oracle database and why? The best companies that makes raid servers?

[URL].........

View 3 Replies View Related

SQL & PL/SQL :: Row Level To Column Level Data Transposition?

May 14, 2010

I have a data like,

1) manual_temp_master

auto_idbatch_id sec_idsec_id_type crrncy_cdcreate_Dt price_dt
------------------------------------------------------------------
11234ABC1CUSIPUSD14/05/201014/05/2010
23456XYZ1SEDOLGBP13/05/201013/05/2010

2)manual_temp_detl

auto_idbatch_id Price_bkt_cdscreate_Dtprice_date
---------------------------------------------------------
11234PS114/05/201014/05/2010
11234PS214/05/201014/05/2010
11234PS314/05/201014/05/2010
11234PS414/05/201014/05/2010

[code]....

I want to write a sql query which will fetch the data from manual_temp_master and manual_temp_detl.But from manual_temp_detl table, Price_bkt_cds columns should be displayed as columns. Like the should look like as below:

sec_idsec_id_type crrncy_cd COL_PS1 COL_PS2 COL_PS3 COL_PS4 COL_PS5COL_PS6price_date
--------------------------------------------------------------------------------------
ABC1CUSIPUSDPS1PS2PS3PS4PS5PS614/05/2010
XYZ1SEDOLGBPPS1PS2PS3PS4PS5PS613/05/2010

View 8 Replies View Related

SQL & PL/SQL :: DBMS_STATS For Table Level Vs Partition Level

Nov 17, 2010

what is the difference on DBMS_STATS for table level and partition level , which will provide the best optimizer . If the table xxxx is partitioned from 1 to 10 ,then running gather stats on table xxxx as whole table level or partition level which will provide best result on the performance.

View 1 Replies View Related

Forms :: 6i - Expiration Of Session-level Database Of Profiles

May 5, 2010

I have an application in Form 6i and I have implemented expiration of Session-level database of profiles, the problem I have is that I do not know in which time expired the session because it shows me no message and the application is open, try to do something and begin to get errors .

View 2 Replies View Related

Server Administration :: How To Get Hardware Information From Database Level

Nov 22, 2010

How Can We Get Hardware Information In Oracle Database 8i for example harddisk size,volume,processor etc. i want database run only specified harddisk

View 1 Replies View Related

Dynamically Define Database Link To Use (at Session Level)?

Sep 1, 2010

Three databases: A, R1, R2. Database A is local and the other two databases are remote. Database A has one db link to each remote database (dblinkR1 and dblinkR2).

In some of my queries I need to get data from the remote databases, but the remote database to use depends on the application user, so, I would like to dynamically use one of the database links.

Something like this:

1 � user logs into the application;
2 � based on the user credentials the application defines the db link to use;
3 � query data using the defined db link.

(for example)

SELECT a.col1, a.col2 FROM table_A@dyndblink a ORDER BY a.col1;

@dyndblink would be a "pointer" to dblinkR1 or dblinkR1.

Is there a way to dynamically define the database link to use (at a session level)?

View 2 Replies View Related

Error In OS Level Authentication For Oracle 11gR2 Database Access?

Mar 18, 2011

We have an issue regarding OS level authentication to access Oracle 11gR2(11.2.0.1) database.

Our environment -
UNIX - AIX 5.3 (OS user id password is having kerberos security).
Oracle 11.2.0.1 (32 bit client) installed in server 1
Oracle 11.2.0.1 (64 bit server) installed in server 2.
Everything works fine when we created a general userid test_db in the database and connect through sqlplus test_db@dbname.

But when we try to use the option of OS level authentication using "sqlplus /", it throws following error and could not be connected.

ERROR: ORA-12545: Connect failed because target host or object does not exist

I have created the same OS user name in database (with external password authentication) with prefix OPS$. we have set ORACLE_SID as well.

View 1 Replies View Related

Forms :: Calling Procedure At (Form / Library And Database) Level

Mar 17, 2011

I've a procedure let us say SALES_PROC on Form Level and same procedure in Library and in Database also.

-How to call SALES_PROC created at Form Level?
-How to call SALES_PROC created at Library Level (.PLL)?
-How to call SALES_PROC created as stored procedure in Database?

View 5 Replies View Related

Data Guard :: Find Out Number Of Standby Databases Configured For Primary Database From OS Level?

Nov 14, 2013

How can find the number of standby databases configured for primary database from os level

View 9 Replies View Related

Backup & Recovery :: Incremental Backup Level 1 Or Level 0 Without Archivelogs?

Oct 22, 2012

Can you take an incremental backup level 1 or level 0 without archivelogs?

syntax would bebackup as compressed backupset cummulative level 1 database.

The reason I ask is because when I run backup as compressed backupset cummulative level 1 database plus archivelogs # it runs fine, but when I run backup as compressed backupset cummulative level 1 database it just hangs.

View 20 Replies View Related

Why Database Trigger Can Became DISABLED

Apr 30, 2013

We have an application, using ddl database trigger on oracle database.Trigger depends on package and several tables, package depends on user defined function.It works on different customer sites well, but for one customer (11g?), sometimes (cannot determine when and why), trigger became disabled.

I know, that trigger can became invalid, when its dependencies changed (i think in this situation oralce probably try to re-compile it). I understand that it can changed to be invalidwhen dependencies are not valid.I know I can alter trigger to became disabled manually.

But when it can became disabledwithout explicit alter statement be run!? Are there some conditions / circumstance when Oracle changed the trigger to be disabled?We'll ask the customer DBA team to enable audit on trigger, but it will take time to be done..

View 1 Replies View Related

Database Trigger To Subtract 1 Day

Mar 14, 2012

I am trying to write a trigger to reduce 1 day from 4 date fields of a table (order)

table name = order
fields = date_1, date_2, date_11, date_12 and dest_id

for example: the trigger will verify if orders that are dropping is for a specific customer (dest_id). if yes, the trigger will subtract one day from these date fields (by updating them); if no, (the dest_id is not for this customer) the trigger will not update anything in the DB.

View 1 Replies View Related







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