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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Nov 19, 2010
how i can chk & delete duplicate rows from a table
View 3 Replies
View Related
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
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
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
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
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
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