SQL & PL/SQL :: Creating Trigger - Check If Data Being Inserted Already In Table And If Not Insert It

Aug 24, 2011

I know this is an old thread and I just started working with triggers. I want to check if the data being inserted is already in the table and if not insert it:

create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
values (:new.user_id, :new.location);
end if;
end;

what if I wanted to keep the user but only update location if the user is already in the table. I've tried doing it this way:

create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)

[code]...

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Trigger (Alter Command) And Creating Meta Data Table

Sep 20, 2011

i need a trigger with alter commands to alter the table structure,it will be captured in a separate meta data table(META)

CREATE OR REPLACE TRIGGER meta_alter AFTER Alter ON SCHEMA
BEGIN
update meta set column_name=:new where table_name=ora_dict_obj_name column_name=:old;
END;
/

Meta table contains Table name and column name..i attached the table data in atext file

View 39 Replies View Related

SQL & PL/SQL :: Entire Insert Script Row Will Be Inserted Into Cbxrdlog Table Sqltext Column

Aug 24, 2011

I have 2 tables. Cbxrd and Cbxrdlog. If the Cbxrd table having creation date column. when it inserts the row. if the column is null value. then the entire insert script row will be inserted into Cbxrdlog table sqltext column.

i have attached the trigger script. when i execute the table but i shows error like

"Ora-0756 Quoted String not properly terminated".
"Ora-04098: trigger os_wm_sit_owner.cbx trigger is invalid and failed revalidation.

View 5 Replies View Related

How To Check And Insert New Data Using Stock Procedure

Feb 11, 2007

i've a problem in using store procedure. My code is to get postcode id when i pass a postcode. First it will check the postcode that i pass if already exist it will get postcode id but if not it will insert new postcode and get a new postcode id created then pass into ASP system. When i try run this stock procedure i got error as below :-

SQL> exec INSERT_PCODE_GMDS
BEGIN INSERT_PCODE_GMDS; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'INSERT_PCODE_GMDS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

(
Postcode1 IN varchar2,
citiID IN Number,
county_ID IN number,
city_name IN varchar2,
sub_cityID IN number,
pcode OUT number
)
[code].......

in ASP to pass and get back the values i used code as below. but i think the problems occurs in my stock procedure

set cmd=Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = OBJdbConnection
cmd.CommandText="INSERT_PCODE_GMDS"
cmd.CommandType= 4
cmd.Parameters.append cmd.CreateParameter("@poskod",adVarChar,adParamInput,5,poskod)
[code].......

View 1 Replies View Related

Creating A Trigger To Populate Another Table

Jan 4, 2007

Having trouble creating a trigger to populate another table.

The SQL:

CREATE OR REPLACE TRIGGER "P_M_YES"
AFTER INSERT OR UPDATE ON DOMAIN
REFERENCING NEW AS NEW.P_M AND OLD AS OLD.P_M
FOR EACH ROW
WHEN (NEW.P_M = YES)
BEGIN
INSERT INTO PAGE_MAKER VALUES(:NEW.D_NAME, :NEW.USER_ID);
END P_M_YES;

/
ALTER TRIGGER "P_M_YES" ENABLE
/

I get an invalid trigger specification.

View 3 Replies View Related

SQL & PL/SQL :: Creating Trigger - Table Or View Does Not Exist

Apr 6, 2012

When i try to create a trigger , i ended up with error.

SQL> create or replace
2 TRIGGER LOGON_TRG AFTER LOGON ON DATABASE
3 BEGIN
4 INSERT
5 INTO
6 user_audit_log
7 SELECT
8 user#,

[code]....

Warning: Trigger created with compilation errors.

SQL>
SQL> show error
Errors for TRIGGER LOGON_TRG:
LINE/COL ERROR
-------- -----------------------------------------------------------
2/5 PL/SQL: SQL Statement ignored
17/17 PL/SQL: ORA-00942: table or view does not exist

The command used to resolve the error is

GRANT SELECT ON v_$session TO jack;

Jack user has sysdba privilege. My question is 'sysdba' is a super and special user which has all the privileges in database. Then why does it need SELECT privilege on v$session to user to create the trigger?

View 3 Replies View Related

SQL Query To Get DATA From Table Inserted In Last 24hrs

Oct 22, 2012

the query to copy the data in last 24 hours from one to another table

Database Version :- Oracle 11g

View 2 Replies View Related

SQL & PL/SQL :: Partial Data Inserted When Reading From Global Temporary Table?

Jun 25, 2012

I have a complex sql query that fetches 88k records. This query uses a global temporary table which is the replica of one of our permanent tables. When I do Create table..select... using this query it inserts only fewer records. But when I make the query point to the permanent table it inserts all 88k records.

1. I tried running the select query separately using temp and perm table. Both retrieves 88k records.

2. From debugging I found that this problem occurred when we were trying to perform a left outer join on an inline view.

However this problem got resolved when I used the /*+ FIRST_ROWS */ hint.

From my limited oracle knowledge I assume that it is the problem with the query and how it is processed in the memory.

View 1 Replies View Related

SQL & PL/SQL :: Trigger To Insert Record Into Another Table

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

SQL & PL/SQL :: Insert Or Update Trigger On Table B

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

SQL & PL/SQL :: Update Table In After Insert Trigger

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

SQL & PL/SQL :: Insert Using Column Name To Indicate Inserted Value

Apr 16, 2012

I have the following tables:

CREATE TABLE test_abc(id NUMBER PRIMARY KEY, col1 NUMBER(10), col2 NUMBER(10), col3 NUMBER(10));

INSERT ALL
INTO test_abc VALUES (1,12345,34567,87654)
INTO test_abc VALUES (2,17345,37567,87754)
INTO test_abc VALUES (3,12745,34767,87674)
INTO test_abc VALUES (4,17045,30567,80754)
INTO test_abc VALUES (5,12740,34067,87604)
SELECT * FROM dual;

CREATE TABLE test_b (id NUMBER, col VARCHAR2(10), coltype_id NUMBER);

What I need to do is to convert the columns col1, clo2, col3 in test_abs into rows in test_b. but I need to do this so that the column name is what determines the value of colytype_id.So in test_b values will look like:

id col coltype
1 12345 1
1 34567 2
1 87654 3

and so on.

View 11 Replies View Related

PL/SQL :: Trigger To Increment A Non-pk Field After Insert In Another Table

Feb 1, 2013

I have three tables.
One for projects, one for volunteers, and a bridge entity for the many to many relationship between the Project and Volunteer.

In Project table, I have a field called, Volunteers_currently_signed_up, which means the number of volunteers currently signed up to participate in a project.

When I add an entry to my bridge entity which is composed of Volunteer_ID and Project_ID, I want the Volunteers_currently_signed_up to increment by 1, where the Project_ID in the bridge entity corresponds to that in Project.

I have very very little PL/SQL, and this is my amateur attempt so far:

CREATE OR REPLACE trigger "BI_Volunteers_currently_signed_up"
BEFORE INSERT OR UPDATE ON Volunteers_in_project
for each row
WHERE Volunteers_in_project.Project_ID=Project.Project_ID;
begin
Project.Volunteers_currently_signed_up += 1;
end;
/

write a trigger that achieves the above

View 7 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

PL/SQL :: Trigger To Insert In One Table From Other And Truncate 2nd Table

Aug 2, 2012

I have table t1 and t1 , I want a procedure that will insert all records from t1 into table t2 and after successfull insert table t1 should be truncated .

If their is any problem in insert in to table t2 , the truncate command should not work .

Truncate command should work only after successfully insert command .

View 3 Replies View Related

SQL & PL/SQL :: Create Trigger To Change Every Insert In Table To Sysdate

Feb 17, 2011

I have table 'A' with column 'ID','NAME','IN_DATE','PHONE','EMAIL'

Now I have to create a trigger such that on every insert in the table 'A' the value of column 'IN_DATE' changes to sysdate.I m not good in PL/SQL

View 4 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

SQL & PL/SQL :: AFTER INSERT Or UPDATE Trigger Failed - Mutating Table Error

Jul 4, 2013

Table A basically has 4 rows of interest, an outside event changes/inserts let's say row 1.

Row 2 and 3 have to be changed in a trigger depending on the new value of 1.(or not if the value stays the same)

Row 4 is the reference for the update and will not be changed.

My first simple AFTER INSERT or UPDATE trigger obviously failed because of the mutating table error.

View 19 Replies View Related

SQL & PL/SQL :: Data Manipulation Insert - Procedure / Trigger Should Delete Initial Record Saved

Aug 30, 2012

i have one table ot_ins_item where user will enter the details of item, grade,item qty , later on user will go and update the same table the details of different grades received for the same item in different columns with qty breakup in 3 different fields it_qty_01 , it_qty_02,it_qty_03 respectively with different grades , what i need is i want is whenever he updates this table with different grades based on data entered in 3 different fields , a procedure or trigger should delete the initial record saved and insert three different rows based on newly updated values , it may be 3 or it may be 2 sometime depending upon input values that many records should be inserted same time controlling the qty's entered in breakup not exceeding the main qty.

CREATE TABLE ot_ins_item (it_ins_no NUMBER,it_no NUMBER,it_grade VARCHAR2(12),
it_code VARCHAR2(12),it_qty NUMBER,it_flex_01 VARCHAR2(12),
it_01_qty NUMBER,it_flex_02 VARCHAR2(12),it_02_qty NUMBER,it_flex_03 VARCHAR2(12),
it_03_qty NUMBER);

create sequence s_it_no start with 1 ;

INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.NEXTVAL,'A','ITEM1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.NEXTVAL,'B','1TEM2',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.nextval,'C','ITEM3',NULL,NULL,NULL,NULL,NULL,NULL);
SELECT * FROM OT_INS_ITEM;
[code]....

View 5 Replies View Related

PL/SQL :: Create Trigger If Any Insertion In TAB1 Then Records Get Inserted Into TAB2?

Oct 30, 2012

i have requirement like this i don't know abt trigger

create trigger with the below:
Tables: TAB1 TAB2
Create a trigger, if any insertion in TAB1 then records should get inserted into TAB2
create a trigger, if any updation in TAB1 then record should get inserted into TAB2
Create a trigger, if any deletion in TAB1 then record should get inserted into TAB2

TAB1 and TAB2 can be any table.

View 2 Replies View Related

Application Express :: Capturing Insert Done And Storing Value Inserted?

Mar 25, 2013

I am trying to insert In multiple table where one tbl PK is FK of other , I achieved that functionality using Instead of Insert trigger.(PK is handled by another trigger) -- main functionNow On successfully insert i need to captured the PK in Hidden variable to pass it on further for more insert on other table (Pk again being Fk to this table)--- secondary function

View 6 Replies View Related

Number Of Rows Inserted Is Different In Bulk Insert Using Select Statement

Jul 1, 2010

I am facing a problem in bulk insert using SELECT statement.My sql statement is like below.

strQuery :='INSERT INTO TAB3
(SELECT t1.c1,t2.c2
FROM TAB1 t1, TAB2 t2
WHERE t1.c1 = t2.c1
AND t1.c3 between 10 and 15 AND)' ....... some other conditions.

EXECUTE IMMEDIATE strQuery...These SQL statements are inside a procedure. And this procedure is called from C#.The number of rows returned by the "SELECT" query is 70.

On the very first time call of this procedure, the number rows inserted using strQuery is 70. But in the next time call (in the same transaction) of the procedure, the number rows inserted is only 50.And further if we are repeating calling this procedure, it will insert sometimes 70 or 50 etc. It is showing some inconsistency.On my initial analysis it is found that, the default optimizer is "ALL_ROWS". When i changed the optimizer mode to "rule", this issue is not coming.I am using Oracle 10g R2 version.

View 3 Replies View Related

SQL & PL/SQL :: Check Every Table For Data?

Oct 30, 2012

I'm working on a feature that needs to check every table for data. At the moment I am roundtripping every time and running the query like this:

SELECT 1 FROM dual WHERE EXISTS(SELECT 'x' FROM table_name)

Is there a better way that allows me to get the result for all tables in one trip? I've tried a correlated subquery like this:

SELECT table_name, (SELECT 1 FROM dual WHERE EXISTS(SELECT 'x' FROM table_name) FROM all_tables

but I think that doesn't work because Oracle looks for a table called table_name rather than the value from the outer query.

View 13 Replies View Related

SQL & PL/SQL :: How To Check And Delete Duplicate Data From Table

Nov 19, 2010

how i can chk & delete duplicate rows from a table

View 3 Replies View Related

SQL & PL/SQL :: Check If Object Exist Or Not While Creating Procedure?

Mar 5, 2012

I have to create a stored procedure having some 10 cursors and i have to display the data's fetched by select statement in cursors using dbms_output.put_line().

for ex:
-------
CREATE OR REPLACE PROCEDURE PROC AS
CURSOR C1 AS SELECT * FROM EMP;
BEGIN
FOR R IN C1
LOOP

[code].....

My question is while creating procedure i want to check if the object exist in database or not.

If EXIST
NO PROBLEM MY CODE CREATE THE PROC AND EXECUTED FINE
IF NOT EXIST

I don't want an exception to be thrown stating "OBJECT DOES NOT EXIST" I wanted PL/SQL engine not to execute the particular select statement itself and continue executing other select statements.

The reason why have such kind of wierd requirement is my program displays all the CEMLI objects in any 11.5.10 instance. I don't know whether they have installed discoverer or not. if they installed it no problem else my program have to eliminate.EXECUTE IMMEDIATE, REF CURSOR becoz i tried it and works fine.but not able to wrap in WRAPPER UTILITY 8.0 versions.

View 14 Replies View Related

PL/SQL :: Create Trigger To Insert Data From One User To Another User In Same Database?

Apr 16, 2013

I Created One Trigger as Follows

CREATE OR REPLACE TRIGGER TRIGGER1
BEFORE INSERT
ON table1
FOR EACH ROW

[code]......

Here , I Want To Insert The Data From My User To Test User . In This Situation When I Execute The Above Trigger It Shows The Error PL/SQL: ORA-00942: table or view does not exist

View 3 Replies View Related

Forms :: Insert And Update Directly Into Table From Pre Update Trigger Of Block?

May 14, 2010

I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:

If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.

Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?

View 4 Replies View Related

SQL & PL/SQL :: Timezone Check Trigger?

Sep 14, 2012

I have an interesting problem. I have a server in the UK and I have three databases globally - UK (FINUKQ1), NY (FINNYQ1) and TK (FINTKQ1). The NY and TK databases will be migrated to the UK server as part of a consolidation project. Obviously, Time Zones and DST are the biggest concern for me.Here was my plan for the implementation (concentrating on Time Zone/DST issues).

Added to .profile:
--------------------
if [[ "$ORACLE_SID" = "FINTKQ1" ]]; then
export TZ="Asia/Tokyo"
elif [[ "$ORACLE_SID" = "FINNYQ1" ]]; then
export TZ="America/New_York"
else
unset TZ
fi
echo "TZ=$TZ"

Once environment has been set start database and listener.

Change database TZ:
-----------------
alter database set time_zone='Asia/Tokyo';
alter database set time_zone='America/New_York';

Check database TZ:
select dbtimezone from dual;

Add trigger:
------------
CREATE OR replace TRIGGER sys.timezone_check
AFTER startup ON DATABASE
DECLARE

[code]...

The trigger was there to put the database in restricted session if it is started in the wrong TZ and an error written to the alert log. The check was done on the dbtimezone and the dbname. Quite simple. Only issue is that it doesn't work as I wanted as dbtimezone and TZ are separate and disparate entities. i.e. I can start the TK database up in UK time (which will be incorrect for this project), and check the dbtimezone and it will show as 'Asia/Tokyo'.

Is there a better way of checking? Any trigger like this to compare TZs etc?

View 2 Replies View Related

Forms :: Read Data From Table And Insert To Another Table With A Cursor?

Feb 20, 2013

I have a table with a BLOB column ;

I want read data from table and insert to another table with a cursor

My code is :

procedure read_data is
cursor get_data is
select id,image from picture1;
id1 number;
pic blob;
begin
open get_data;

[code]....

when I run form , error FRM-40734 occurred

error in line " fetch .... "

View 1 Replies View Related

SQL & PL/SQL :: Create Trigger That Will Update Table When There Is Insert / Update

May 29, 2012

i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".

*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

*Action: Rewrite the trigger (or function) so it does not read that table.

CREATE OR REPLACE TRIGGER set_date_end
BEFORE INSERT OR UPDATE OF issued ON shares_amount
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO shares_amount(date_end) VALUES(SYSDATE);
END set_date_end;
/

View 3 Replies View Related







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