SQL & PL/SQL :: Prevent Duplicate Column Value Using Trigger

Mar 8, 2010

I want to avoid duplication on old_nic_no and new_nic_no number in a Table, for this i am using a following trigger

create or replace TRIGGER chk_ip_duplications
BEFORE UPDATE
ON cb_insured_person_bak
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

[Code]...

when i use following command

update cb_insured_person_bak set new_nic_no = '34601-0774284-9' where eobi_no = '4300G043746'

34601-0774284-9 already exist with another eobi_no , i expect error described in rise_application_error clause but i give following error

ORA-04091: Table cb_insured_person_bak is mutating..........

How i can resolve this problem.....

View 15 Replies


ADVERTISEMENT

SQL & PL/SQL :: Preventing Duplicate Column Value Using Trigger?

Mar 18, 2010

I am working on a assignment, How to prevent duplication with Trigger. I create following compound trigger for this, but it is not doing so.

CREATE OR REPLACE TRIGGER CHK_DUPLICATION FOR INSERT ON TST COMPOUND TRIGGER
R_BCOUNT NUMBER;
R_ACOUNT NUMBER;
D_VAL NUMBER;
BEFORE STATEMENT IS
BEGIN

[code].....

View 12 Replies View Related

Forms :: Prevent Duplicate Record Entry Date Wise?

Oct 10, 2011

we have a table attendance_d with no constraint which have duplicate emp_id we want to stop duplicate emp_id on the same date. if employee's record already entered in today's date then duplicate Error message must show if he tries again to enter the same record. for this i have written the following code but it is not working date wise some body. i want to use on WHEN VALIDATE ITEM TRIGGER in oracle forms 6i.

DECLARE
l_count NUMBER;
BEGIN

[Code]....

i have tried my best to format the syntax of code but in preview it showing like as above i have formated in toad by using the key ctrl+shift+f.

View 2 Replies View Related

SQL & PL/SQL :: To Create A Trigger To Prevent Transactions

May 10, 2013

Im trying to create a trigger to prevent transactions like insert, update and delete based on two conditions:

1) Friday
2) Between 5pm to 8am

View 5 Replies View Related

Can Trigger Prevent Insert And Update To Table

Mar 4, 2013

We would like to create functions to insert and update our tables and would like to make it not possible to update and insert the table directly outside of the function. Is there a way to do that in the trigger?

View 1 Replies View Related

Server Administration :: How To Prevent DBA User From Disabling Any Trigger

Jun 19, 2011

How can prevent DBA user from disabling any trigger ? suppose DBA user can't be able to execute following command

ALTER TRIGGER {trigger_name} DISABLE;

how can i protect it?

View 3 Replies View Related

Application Express :: Prevent Reset For Default Search Column In Interactive Report

Jul 19, 2013

I have created a IR report and I set the default search column  using the suggestion found in the thread URL....but how can I keep this default even after the research was done?Now it is resetted ! 

View 1 Replies View Related

SQL & PL/SQL :: Divide One Of Column Value With Other As Duplicate

Feb 6, 2012

below are the create and insert statement, making duplicate row, i mean In table emp_detail, we have row like

ENAMEJOBDEPTNODETAIL
RAJ CS 10 RAJ IS IN CARE OF ROHIT

We need to insert the same record but divide the last column value on the basis of "IN CARE OF" any word after this should come in next new row with the same value for all other column, like

ENAMEJOBDEPTNODETAIL
RAJCS10RAJ IS IN CARE OF
RAJCS10ROHIT
CREATE TABLE "EMP_DETAIL"
("ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"DEPTNO" NUMBER(2,0),
"DETAIL" VARCHAR2(100)
[code]...

View 3 Replies View Related

SQL & PL/SQL :: Duplicate On A Single Column

Sep 23, 2010

I have a table with multiple columns and I need to retrieve the ones where column a stores duplications for records where column b is discrepant

this is the table

NAME_ID PHONE_NUMBER
12345 +41 22 595 5555
12345 +41 22 595 5555
12342 +41 22 595 5500
12340 +41 22 595 5555

the query should return

NAME_ID PHONE_NUMBER
12345 +41 22 595 5555
12340 +41 22 595 5555

The closest I got was with the below, but this also returns duplicates within the same NAME_ID.

select phone_number, name_id
from name_phone
where (phone_number) in
(select phone_number
from name_phone
group by phone_number
having count(*) > 1)
group by phone_number, name_id
order by phone_number

View 3 Replies View Related

SQL & PL/SQL :: Trigger Which Changes Content Of One Column Based On Information Within Another Column

Sep 10, 2010

I am trying to create a trigger which changes the content of one column based on the information within another column.

For Example, if the 'ITEMQUANTITY' field drops below 1 then I want the STATUS column to say 'Out Of Stock'.

View 23 Replies View Related

SQL & PL/SQL :: Finding Duplicate Only Records In More Than One Column?

Dec 16, 2010

I am trying to write SQL which finds records which are duplicated in more than one column.

Requirement : When ever i have duplicates in Col2 and Col3 both i need that record...

My Source table:

COL1COL2COL3COL4
163kg87
263fh87
1ab23
2ab24
3cd98
4fg87
5xy77
6xy67

Desired Output

COL1COL2COL3COL4
1ab23
2ab24
5xy77
6xy67

For Table generation

CREATE TABLE TEMP_TEST
(
COL1 NUMBER,
COL2 VARCHAR2(10 BYTE),
COL3 VARCHAR2(10 BYTE),
COL4 NUMBER
)

Insert statements

Insert into TEMP_TEST
(COL1, COL2, COL3, COL4)
Values
(163, 'k', 'g', 87);
Insert into TEMP_TEST
(COL1, COL2, COL3, COL4)
Values

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Duplicate Rows Displayed For A Column

Mar 30, 2013

column sid format 'a5'
column serial# format 'a10'
column mins_running format 'a15'
column sql_text format 'a100'
set linesize 200
set pagesize 30

[Code]..

I am running this code, and the output shows multiple lines.

TRIM(S.SID) TRIM(S.SERIAL#) MINS_RUNNING SUBSTR(Q.SQL_TEXT,1,70)
---------------------------------------- ---------------------------------------- --------------- ----------------------------------------------------------------
700 46592 242.08 Select count(*) as count, case when count(*)>0 then 'FAIL' else
700 46592 242.08 'PASS' end as result
from (SELECT cv.code_value
FROM code_valu

[Code]...

Is there a way to wrap up the column for SQL_TEXT VARCHAR2(64) so that I can 1 row for the output?

View 14 Replies View Related

PL/SQL :: Check For Any Duplicate Values In COUNT() Column

May 13, 2013

I'm going to do some testing, and for that I require to retrieve some data based on a single column e.g test_data_col, which -

1. Has 3 or more count(test_data_col) for a given set of group by columns e.g grp_col1, grp_col2, grp_col3
2. Within the set of rows retrieved, that particular column holds some duplicate values. I don't need the duplicates displayed, just know if duplicates exist or not.

This might explain what I'm trying to do -

grp_col1, grp_col2, grp_col3, test_data_col

1, A, xyz, HELLO
1, A, xyz, HELLO
1, A, xyz, BYE
1, A, xyz, GOODBYE

2, C, pqr, WELCOME
2, C, pqr, GOOD MORNING
2, C, pqr, BAD MORNING

So for condition 1, I do something like this -

SELECT COUNT(test_data_col) cnt, grp_col_1, grp_col2, grp_col3
FROM test_tab
GROUP BY grp_col_1, grp_col2, grp_col3
HAVING COUNT(test_data_col) >= 3;

In this same query, I want to do something that will tell me if the aggregate COUNT(test_data_col) has any duplicate values within it. Again, displaying the duplicates is not important here.

SELECT COUNT(test_data_col) cnt, grp_col_1, grp_col2, grp_col3,
/*some logic*/ dup_val
FROM test_tab
GROUP BY grp_col_1, grp_col2, grp_col3
HAVING COUNT(test_data_col) >= 3;With the proper coding to replace /*some logic*/, I get following values -

cnt, grp_col_1, grp_col2, grp_col3, dup_val

4, 1, A, xyz, Y
3, 2, C, pqr, N

I just gave dup_val column to explain what I'm trying to achieve.. any other way to know the existence of duplicates in the count aggregate will be fine.My Oracle version is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0

View 2 Replies View Related

Forms :: Duplicate Updatable Column On 2 Canvases

Mar 28, 2013

I have a form which utilizes 2 canvases that the user can toggle between. There is a database column that I would like to have appear on both and be updatable from either place. In my search here first, I found where I could set up a non-database item and copy to it at the point of Post-Query...and that comes close, but I need both columns to not just reflect the db column but be able to update it.

I am about to try using a second trigger to move things from a non-DB column to a DB-column next, but just wondered if there is a better way. When I first compiled with the designer the duplicate column I set up as a DB column also. It only gave me warnings (that I could have lived with) but the ultimate compile my system does outside of the designer calls it an error.

View 7 Replies View Related

PL/SQL :: Error When Creating A Table / ORA-00957 - Duplicate Column Name

Mar 23, 2013

I'm getting an error as follows .

create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
where
a.CNTCT_KEY = s.CNTCT_KEY and a.SCN_NUM=s.SCN_NUM
and a.ACTVTY_DT = to_date('03/17/2013','mm/dd/yyyy')
and s.SCN_OPEN_DT = to_date('03/15/2013','mm/dd/yyyy')
and a.SRC_SYS_DESC = 'FACET');
create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
*
ERROR at line 1:
ORA-00957: duplicate column name

View 1 Replies View Related

Application Express :: Duplicate Column IDs In Reports On Same Page

Mar 27, 2013

I have 4 reports on the same page. I have added a checkbox column to each one. On clicking I want to populate a collection with the value of the id of the corresponding row and then have a button acting on the whole list.

My code to get the id of the item that contains the value I want is this

id = 'f02_' + $(this.triggeringElement ).attr('id').substr(4,4);

But there is an f02_0001 for each report, and the code just finds the first one, rather than the one in the context of the report being clicked.

<input name="f02" id="f02_0001" type="hidden" value="2072"/>
....
<input name="f02" id="f02_0001" type="hidden" value="2052"/>
...
etc etc.

All that is ever picked up is 2072

They have different table ids but I don't know how to reference the correct ones in my code.

View 3 Replies View Related

SQL & PL/SQL :: PLS-00402 - Alias Required In SELECT List Of Cursor To Avoid Duplicate Column

Oct 25, 2013

I am getting [Error] PLS-00402 (182: 1): PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names error in my SP.I have created alias for each column and still i am getting the error.

for my_rec_lot in
(SELECT LLP.BOOK_VALUE LLP_BOOK_VALUE,LLP.COMMISSION LLP_COMMISSION,LLP.CURRENCY LLP_CURRENCY,LLP.EXCHANGE_RATE LLP_EXCHANGE_RATE,LLP.EXPENSES LLP_EXPENSES,

[Code].....

View 6 Replies View Related

SQL & PL/SQL :: Trigger On Particular Column

Dec 27, 2011

i have a table with 46 columns

i want to fire update trigger except update on one column (named EMP_ID)

how to write ???

i tried this but this is too Large

create or replace trigger aft_update_employee

after update of [ that 1 to 45 columns other then emp_id ] on employee
(this query is so large )

View 8 Replies View Related

SQL & PL/SQL :: Holding Up Trigger For A Particular Column

Aug 5, 2013

The trigger should fire on the emp table if somebody does any changes to all columns except ename column.

View 23 Replies View Related

SQL & PL/SQL :: Inserting Values For All Column Names In Trigger

Aug 10, 2010

I am working on convertion of triggers from SQL SERVER to Oracle. I got them converted using a tool. But not sure how to correct the below trigger which uses '*' in sql server.

Code in SQL SERVER :-

CREATE TRIGGER INSERT_AUDIT ON T_AUDIT
FOR INSERT AS
INSERT INTO T_AUDIT_LOG
SELECT 'INSERT','AFTER', CURRENT_TIMESTAMP, NULL,*
FROM INSERTED

Converted into Oracle:-

CREATE OR REPLACE TRIGGER INSERT_AUDIT
BEFORE INSERT
ON T_AUDIT
FOR EACH ROW
INSERT INTO T_AUDIT_LOG
VALUES ( 'INSERT', 'AFTER', SYSTIMESTAMP, NULL, * );

END;

how can I remove the *. I tried replacing the * with the rest column names prefixing with :NEW.

the last 3 columns when are c_data1,c_data2,c_data3 as :-
INSERT INTO T_AUDIT_LOG
VALUES ( 'INSERT', 'AFTER',SYSTIMESTAMP NULL, :NEW.c_data1,:NEW.c_data2,:NEW.c_data3);
But this gives error:-
PLS-00049: bad bind variable 'NEW.C_DATA1'
PLS-00049: bad bind variable 'NEW.C_DATA2'
PLS-00049: bad bind variable 'NEW.C_DATA3'

how can I convert this code.

View 7 Replies View Related

SQL & PL/SQL :: Create Trigger On Certain Column For Table Structure

Nov 3, 2011

create trigger on certain column for table structure.

SQL> desc MXMS_BF_TXN_DTL_T
Name Null? Type
----------------------------------------- -------- ----------------------------
DOC_NO NOT NULL VARCHAR2(200)
SEQ_NO NOT NULL NUMBER(24)
GL_CODE VARCHAR2(200)
TXN_NATURE VARCHAR2(200)
TXN_TYPE_CODE VARCHAR2(200)
[code].....

I need to collect new and old data whenever update statement fire on DOC_NO,POLICY_KEY,CRT_USER column.i have created only audit table for the above as below structure .

Name Null? Type
----------------------------------------- -------- ----------------------------
TIMESTAMP DATE
WHO VARCHAR2(30)
CNAME VARCHAR2(30)
OLD VARCHAR2(2000)
NEW VARCHAR2(2000)

Description:- TIMESTAMP is for when the modification happen.
WHO is for username
CNAME is for column which is modified
OLD is for old value for the modified column
New os for new value for the modified column

View 3 Replies View Related

PL/SQL :: How To Enter Value Based On Condition In Column Without Using Trigger

Feb 5, 2013

while replicating form mssql 2005 it is entering space for null in oracle clob col.so i wanted to know.

1)can check constaint modify the content of column on which it is defined?

like i want to enter null in a column , if entered date is greater than current date else the entered date.i do not want to use triggers or client side script.

View 1 Replies View Related

Forms :: ORA-01461 When Updating A LONG-Column Via Trigger

Jul 5, 2010

I have a non-base-table item which I want to update in the pre-update trigger of the current block.

If the content of the field exceeds 4000 characters, i get the error message

ORA-01461: can bind a LONG value only for insert into a LONG column.

The code is

update tab set long_col = :formsblock.long_col
where tab.tabpk = :formsblock.foreign_tabpk;

Workarounds would be,
1.) to delete the old dataset and insert the new one:

delete from tab
where tab.tabpk = :formsblock.tabpk;

insert into tab (tabpk, long_col) values
(:formsblock.foreign_tabpk, :formsblock.long_col);

or 2.) to change the Item from a non-database item to a database item and use the internal update of the forms-module, but both workarounds are not very satisfying.

Do you know another way to update the LONG-column within the pre-update trigger (or any other PL/SQL part of forms)?

View 2 Replies View Related

PL/SQL :: Prevent Overlapping Date

Nov 16, 2013

I use Oracle 11.2.0.3.I have APPOINTMENT table:(ID NUMBER PK , FROM_TIME TIMESTAMP , TILL_TIME TIMESTAMP , NOTE VARCHAR2 (200))I want to prevent end users from making an appointment that overlaps with other appointments. I created this trigger: 

Create or replace TRIGGER  "APPOINTMENT_CK"
BEFORE
  INSERT OR UPDATE ON APPOINTMENT  FOR EACH ROW
BEGIN 
IF
[code]....

But, it looks like I cannot use column name here.

View 11 Replies View Related

Forms :: Prevent Deletion Of Record

May 6, 2010

Form has insert and delete enabled.

I would like to prevent the deletion of a record based on some column value.

Something like:

IF (COLA value < 10) THEN
SHOW ALERT;
RECORD IS NOT DELETE
ELSE
DELETE RECORD AS NORMAL
END IF;

I want this to happen even before the record is marked for deletion, but I don't know where to put it. I tried a pre-delete trigger, but it does not work they way I want.

View 8 Replies View Related

Forms :: Prevent Item From Being Copied?

Jan 31, 2011

how can i prevent an item from being copying. I mean to say user will not able to copy the value from the item and paste in notepad.

The item will be enabled also i can not desable it.

View 9 Replies View Related

Prevent Other Sessions From Writing To The Tables In Procedure?

Jun 26, 2013

here' my code.

delimiter //
SELECT CONNECTION_ID()//
LOCK TABLES source.jos_daikin_control_card_fcu_model WRITE//

[Code].....

ERROR 1192 (HY000): Can't execute the given command because you have active lock
ed tables or an active transaction

Is there a way to prevent other session from accessing the tables called in the procedure?

View 3 Replies View Related

Security :: Prevent Users From Updating Tables

Sep 9, 2010

i've created a schema, which contains 30 tables. the tables are accessed & updated through a web based application. i've made public synonym for all tables for some reasons. how can prevent any one other than application users from updating the tables from Sqlplus.

View 2 Replies View Related

Application Express :: Prevent Changing Value Of Item?

Sep 17, 2012

Im my application, after login, I have a hidden & protected item whose value I am setting based on a logic. Also I would like to access this item's value in a application process, so session will have this value. Now I do not want anybody to be able to change the value of this item. For Eg, if my item was P1_RESP, I was able to change its value in the following way, I have an edit link on somr IR, where I redirect to another page passing some values and the URL looks like : [URL] After the browser loads, I can edit the above URL to below and click enter, [URL]

View 1 Replies View Related

Server Administration :: Prevent Bilingual Characters In Database?

Jan 22, 2010

we are using oracle10g version. Currently my character set is WE8MSWIN1252 At present, somehow, we have non-english characters(for example spanish) in the database. We wanted to stop entering these kind of characters.. Can we prevent this by changing the current character set?

View 2 Replies View Related







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