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
ADVERTISEMENT
Oct 28, 2010
I am trying to use a CASE statement
CASE WHEN (EP.ORDER_NUMBER IS NOT NULL AND ATD.IS_DISCONNECT_CREDIT = 1 ) THEN
.........
......
END
In my case when I club two conditions after WHEN in case statement , everytime the first condtion is satisfied (i.e EP.ORDER_NUMBER IS NOT NULL) it is entering into the loop.
Irrespective of using AND or OR after the first condition I am getting the same results.
I want to enter the CASE statement only when both the conditions (1st and 2nd )are satisfied
View 10 Replies
View Related
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
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
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
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
View Related
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
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
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
Aug 10, 2011
HOW to use variable P_TMPLID in following statement
TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;
because its throwing error while compiling
and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION
View 2 Replies
View Related
Jul 1, 2011
How can "call one trigger of item in trigger of form"
View 5 Replies
View Related
Sep 13, 2013
In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition
ON(source.DNO = target.DNO
AND source.BNO=target.BNO);
I thought that using UNIONALL for select statement of the schemas as below.
SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....
View 5 Replies
View Related
Jan 11, 2012
I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).
When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:
SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...
From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?
View 10 Replies
View Related
May 28, 2013
I am having the following table and values.
A | B
=====
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
I need an output in following
A | B | C
=========
1 | 1 |
2 | 2 | 3 --> 1+2
3 | 3 | 6 --> 3+3
4 | 4 | 10 --> 6+4
5 | 5 | 15 --> 10+5
I mean add the first row of column b with second row of column ==> this output need to show in column c
again this output need to add with third row of column b .......
any sql commands / keywords for this
View 2 Replies
View Related
Jul 11, 2007
how do I add a column to an existing table which when rows are added to the table this column will have the same default value?
alter table thetable add (new_column_name varchar2(100) default bbubu);
View 1 Replies
View Related
Mar 31, 2010
I've been trying to write some code to add a column if it does not exist as the code will be run numerous times and will be parameterized in other software to run across multiple tables.
Here is what I have made so far:
DECLARE
COLEXISTS integer;
vCmdStr varchar2(4000);
[Code]....
I can't figure out where the invalid character is.
View 3 Replies
View Related
Jan 19, 2011
I have a table with zip codes and their plus four values. For ex: zip code of 10000, which has corresponding plus four values of 001, 002, 003, and 008, 009, 010. The issue is just that--a zip code can have sequential plus four values, and then it will skip several potential plus four values, and then start again. I would like to assign a low plus 4 value and high plus four value to a zip code, keeping in mind that the plus four values are not always sequential. So, it would be similar to this:
zip plus4 low plus4 high
10000 001 003
10000 008 010
View 2 Replies
View Related
Aug 7, 2012
I had created a table which have 100s' of entries in it.
create table reg_user
(
USERNAME VARCHAR2(50),
PASSWORD VARCHAR2(20)
)
***
[Code]..
1. In this table, how to update the Nth row, how can I do it
2. Now I need to add USERID in this table,which will get value from 1 to max no. of rows. I do not want to drop the table and again re create it adding USERID or update each row manually. Is there any other way to add USERID and have IDs from 1 to max IDs.
View 30 Replies
View Related
Dec 23, 2012
I have DB on cluster and it set to active/active. ASM disk currently using raw device and connect to SAN storage. So i want to add new ASM disk on current environment.
Does oracle have this doc? What i can see from their said just show adding new ASM to new installation. Does it same as adding new ASM disk to current system? Does it success on implementation?
View 1 Replies
View Related
Sep 15, 2010
select bill_no from bill_mst is showing 25 records.
select bill_no from bill_mst order by 1 is showing 5 records.
View 13 Replies
View Related
Oct 20, 2010
I am trying to run an SQL query which refers/joins around 10 tables.In my case I would want to add an if-else or case condition in the WHERE clause of select query
For example
select a.column1, b.column1, c.column1, d.column1
from a, b, c, d
WHERE a.column2 = b.column2
AND (if a.column3 = 10 then ( I want 2 conditions to be
AND b.column2 = c.column2) added
else after WHERE clause as AND...)
AND b.column2 = d.column2
)
AND d.column2 = a.column2
View 5 Replies
View Related
Aug 8, 2011
When I added a new node to RAC (existng 2 windows server 2008 R2 nodes), using the command,
CODEaddNode -silent
"CLUSTER_NEW_NODES={node3}" "CLUSTER_NEW_PRIVATE_NODE_NAMES={node3}"
"CLUSTER_NEW_VIRTUAL_HOSTNAMES={node3-vip}"
the output was "The cluster node addition <path> was successful".
But when I tried the following command,
CODEcluvfy stage -post nodeadd -node3
I got an error stating
QUOTE "ERROR: Shared resources check for node addition failed
The following node is not in cluster: node3
Post-check for node addition was unsuccessful on all the nodes."
When I checked the inventory logs, I found the following entry
QUOTE SEVERE:
oracle.sysman.oii.oiix.OiixException: The Oracle home '<path>' could not be updated as it does not exist.
at oracle.sysman.oii.oiic.OiicBaseInventoryApp.getOracleHomeInfo(OiicBaseInventoryApp.java:645)
at oracle.sysman.oii.oiic.OiicUpdateNodeList.doOperation(OiicUpdateNodeList.java:206)
at oracle.sysman.oii.oiic.OiicBaseInventoryApp.main_helper(OiicBaseInventoryApp.java:790)
at oracle.sysman.oii.oiic.OiicUpdateNodeList.main(OiicUpdateNodeList.java:399)
View 1 Replies
View Related
Apr 26, 2013
Can we add 10000 days in SYSDATE using + operator ?
Quote: This I can test it but I don't have database access now that's why I am asking this question
View 5 Replies
View Related
Jul 8, 2013
view the below select statement..why it's adding extra zero's...
select to_timestamp('2001-05-22 12:00:18.600','YYYY-MM-DD HH:MI:SS.ff3AM') from dual
output: 5/22/2001 12:00:18.600000000 PM ---why it's adding extra zeors's
my output should be as " 5/22/2001 12:00:18.600 PM"
View 4 Replies
View Related
May 23, 2009
I created a table but I want to add the Unique check to it as I forgot to apply it to the table when I created it.Is it possible to make the field Unique after having created the table or do I have to drop the table and re-create it?
View 3 Replies
View Related
Mar 6, 2009
column name: extra_hour
datatype: varchar2(5)
extra_hour
----------
01:30
01:30
00:00
02:45
and so on...
Problem: I want to add up all the given value and display it in a select statment, that is the output should be:
Output:
XXXXXX
----------
05:45
View 4 Replies
View Related
Aug 12, 2013
I want to add column to table which has huge amount of data and fill with data from another table. What is the best way to do it? Is it faster to use CTAS instead of ALTER TABLE ADD COLUMN?
View 2 Replies
View Related
Aug 17, 2012
Ive got a look up table and i want it to display text instead of just the ID number, how would i do that? so for example if
1 = Hello
2 = Bye
it would display "Hello" instead of "1"
View 8 Replies
View Related
Jul 8, 2010
I want to add some new columns in an existing table that has over 10 millions rows, but not at the end of existing columns, my requirement is such i want new columns between existing existing columns. Is it possible.....
View 16 Replies
View Related
Oct 16, 2012
I checked and found we have disk that is assigned with 0 disk GROUP_NUMBER. What does that mean ? how to check if disk T1_ASM05 is been part of any disk group or not.?
SQL> select GROUP_NUMBER,NAME from v$asm_diskgroup;
GROUP_NUMBER NAME
------------ ------------------------------
1 DATA
2 FRA
SQL>
SQL> select GROUP_NUMBER,name,PATH from v$asm_disk;
GROUP_NUMBER NAME PATH
------------ ------------------------------ -------------------------
1 T1_ASM01 ORCL:T1_ASM01
2 T1_ASM02 ORCL:T1_ASM02
0 ORCL:T2_BACKUP01
0 ORCL:T1_ASM04
0 ORCL:T1_ASM03
0 ORCL:T1_ASM05
6 rows selected.
SQL> !
/home/oracle > /etc/init.d/oracleasm listdisks
T1_ASM01
T1_ASM02
T1_ASM03
T1_ASM04
T1_ASM05
T2_BACKUP01
/home/oracle >
View 1 Replies
View Related
