SQL & PL/SQL :: Write Trigger On Table Which Contain Lot Of Parameters?
Apr 17, 2012
I have to write a trigger on a table which contain lot of parameters.But i need to pick a specific row and check that without disturbing other stuffs.Is there a way to write Before update trigger on a particular rows filtering the unneccasary rows.
The requirement is when user update the date from front end (Java application) the trigger should check the date and validate that it should be month end date. For example.
1)04/21/2012 wrong date
2)04/30/2012 correct date
3)03/29/2012 Wrong date
4)03/31/2012 correct date
View 7 Replies
Jun 29, 2010
I am trying to write a trigger on a new table. (dest_test) This is the first trigger that I have ever attempted (fairly new DBA) and I am having some trouble with the trigger body.It is a before insert trigger that will need to select from another table (dest) for a particular value being inserted (destination).
create table dest_test (
destination varchar2(4) not null,
db_name varchar2(10) not null
)
desc dest
[code]...
I am getting the exact opposite results than I want, though. If the value appears in dest, it is inserting into dest_test... NOT whatI want it to do!If the value doesn't appear in dest, it is throwing ora-6512 and ora-4088 errors. Is there a way to suppress these errors, or to graceful exit from the block so that the trigger completes without throwing these errors?
View 3 Replies
View Related
Apr 12, 2011
I am using SQL*Plus to run this Oracle10g. Here is my problem I have to solve: After a new record is added to the task table, write a trigger that reports the new project balance to the user while updating the customer balance to reflect this additional task cost. Insert a new record to show the trigger works. I somewhat understand triggers,I think I have to combine tables to solve this. Here are my tables/attributes I can use to solve it:
CREATE TABLE customer
(
custnoNUMBER(3)CONSTRAINT ccustpk NOT NULL PRIMARY KEY,
custname CHAR(20) CONSTRAINT ccustnamenn NOT NULL,
phone CHAR(12),
curr_balance NUMBER(9,2) CONSTRAINT ccustbalancenn NOT NULL
[code]...
View 8 Replies
View Related
Aug 5, 2013
Create a function which will indicate if a given record in a table is unique or not. Unique means the data is occurring only once in the entire table.
Function should be in this signature
function IS_UNIQUE (tableName in varchar2,tableAttribute in varchar2)
return number
.....
begin
//logic to check if given data is unique
return 0; //return 0 if data is unique
else
return 1; //return 1 if data is duplicate
end;
Once I run this query
select attribute1 from table1 where IS_UNIQUE(table1,attribute1)=0
All records of attribute1 which are unique need to be fetched. Similarly, select attribute1 from table1 where IS_UNIQUE(table1,attribute1)=1 should return all records of attribute1 which are duplicates.
View 13 Replies
View Related
Aug 16, 2011
I have a table revenue
create table revenue
(
person varchar2(23),
month varchar2(3),
rev_amt number
)
and i have data in a file like below
Person Jan Feb Mar Apr Mai Jun Jul Aug Sep Oct Nov Dez
--------------------------------------------------------
Schnyder,345,223,122,345,324,244,123,123,345,121,345,197
Weber,234,234,123,457,456,287,234,123,678,656,341,567
Keller,596,276,347,134,743,545,216,456,124,753,346,456
Meyer,987,345,645,567,834,567,789,234,678,973,456,125
Holzer,509,154,876,347,146,788,174,986,568,246,324,987
Müller,456,125,678,235,878,237,567,237,788,237,324,778
Binggeli,487,347,458,347,235,864,689,235,764,964,624,347
Stoller,596,237,976,876,346,567,126,879,125,568,124,753
Marty,094,234,235,763,054,567,237,457,325,753,577,346
Studer,784,567,235,753,124,575,864,235,753,864,634,678
i want to load it into the table in the following way.
Person Month Revenue
-------------------------
Schnyder Jan 345
Schnyder Feb 223
Schnyder Mar 122
Schnyder Apr 345
Schnyder Mai 324
Schnyder Jun 244
Schnyder Jul 123
Schnyder Aug 123
Schnyder Sep 345
Schnyder Oct 121
Schnyder Nov 345
Schnyder Dez 197
........ ... ...
How to write control file to load this data into the above revenue table.
View 2 Replies
View Related
Oct 20, 2011
I am trying to create a procedure that inserts parameters into a table and then returns the number of rows inserted back to calling block. the procedure is compiling fine but is not returning the number of rows inserted. My code is as follows;
HOST VARIABLE DECLARATION
VARIABLE g_CarMasterInsertCount NUMBER;
STORED PROCEDURE
CREATE OR REPLACE PROCEDURE CarMasterInsert_sp (
registration IN VARCHAR2,
model_name IN VARCHAR2,
car_group_name IN VARCHAR2,
date_bought IN DATE,
cost IN NUMBER,
miles_to_date IN NUMBER,
miles_last_service IN NUMBER,
status IN CHAR,
rowsInserted OUT NUMBER)
[code]....
I think im close just that my syntax is off.
View 8 Replies
View Related
Aug 28, 2011
My problem is the following:
At the time when P_delivery_date changes in P_ORDERS I want to transfer P_delivery_date to S_delivery_date in S_ORDERS for corresponding records.
Tables:
purchase orders table P_ORDERS:
P_order_number,
P_poz_number,
S_order_number,
S_poz_number,
P_delivery_date
sales orders table S_ORDERS:
S_order_number,
S_poz_number,
S_delivery_date
My question is:
Is it possible and what would be the solution using the TRIGGER on table P_ORDERS to update S_delivery_date with P_delivery_date in S_ORDERS?
View 2 Replies
View Related
May 4, 2011
i want to write, just ONE trigger that handles all DMS events at schema level and writes the field changes to a specific log table. i wanna to write like 'FOR EACH FIELD' as for each row (& also each table). it turns in the fields section and if the 'old' and 'new' values isnt same, it triggers this function & writes changes to log.
ex. is like:
FOR EACH ROW
FOR EACH FIELD
if (old.value != new.value)
insert into LOG(..., old.name,new.name) values (..., old.value, new.value)
NEXT // FIELD
NEXT // ROW
View 19 Replies
View Related
Feb 1, 2013
Is it possible to create table using trigger?
I tried but failed...
CREATE OR REPLACE TRIGGER tri1
AFTER UPDATE
ON dept
FOR EACH ROW
declare
pragma AUTONOMOUS_TRANSACTION;
BEGIN
execute immediate 'create table dept_dum as select * from dept';
END;
/
SQL> update dept set deptno=23 where deptno=10;
update dept set deptno=23 where deptno=10
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TRI1", line 6
ORA-04088: error during execution of trigger 'SCOTT.TRI1'
I am running this as scott user
My requirement is to
create table as select * from table_name where flag=1
This has to be done parallel for all the tables for which this flag is enabled and by that trigger delete all those rows which were backed up as table.
View 4 Replies
View Related
Jul 22, 2010
I created a DDL trigger which manipulates columns of a table after an "alter table" statement.
For us this was all the time
"alter table XXX add NEW_COL varchar2(20)"
or
"alter table XXX modify NEW_COL varchar2(20)".
Unfortunatly we have now a requirement which statements "alter table XXX drop NEW_COL varchar2(20)".
My trigger works fine - you see no problem directly after statement execution but the table is then in an invalid status.
The connection will be destroyed after you tried to see the data of this table.
Also the export (creating a dump) will not work.
The problem I see is that I the database will do a "modify" on a column which has internal the status "dropped".
How I can get the information what kind of alter table statement will be executed?
Or is there any chance to select only columns from USER_TAB_COLUMNS without the "dropped" flag?
create or replace trigger TRIGGER
after alter
on SCHEMA
declare
vCharSet NLS_DATABASE_PARAMETERS.VALUE%TYPE;
begin
-- Select all columns of a table
select COLUMN_NAME, DATA_TYPE, DATA_LENGTH
from USER_TAB_COLUMNS
where TABLE_NAME = vTABLE_NAME; ...
-- Loop for all columns from our select
...
vSTATEMENT := 'alter table ' || vTABLE_NAME || ' modify ' || VOBJECTREC.COLUMN_NAME || ' ' || VOBJECTREC.DATA_TYPE || '(' || vNEW_DATA_LENGTH || ')';
--For example:
-- alter table XXX modify NEW_COL varchar2(20)
execute immediate vSTATEMENT;
exception
...
end;
/
View 11 Replies
View Related
Mar 12, 2012
I have looked at the code you pointed me to, and have attempted to get it to work using a package, but I cant even get the package to compile..
CREATE OR REPLACE PACKAGE BODY trigger_api AS
PROCEDURE tab1_row_change (p_numass IN varchar2,
p_datcre IN date) IS
BEGIN
INSERT INTO tempjob (numass, datecre) VALUES (p_numass, p_datcre);
END tab1_row_change;
[code]....
Doing this process from code is not an option and MUST happen automatically via triggers.The mutating trigger error can sometimes be avoided: URL....
View 1 Replies
View Related
Mar 9, 2012
I am trying to create a trigger which does the following : A flag in the initial able is set to Y. When this happens, the record needs to be inserted into a history table and then DELETED from the calling table.
It must happen in triggers, but I keep getting the mutating error.I have tried to use a Compound trigger, but with no luck and just dont really understand how to get this to work.
Doing this process from code is not an option and MUST happen automatically via triggers.
View 1 Replies
View Related
Aug 20, 2012
We are running an Oracle 10g server.
We are adding support for barcode scanner in one part of our information system. So that the mechanics can add parts to an work orders bill of material themselves using barcode scanner to scan the part and enter the qty.
I can via SQL add a part to the bill of materials and reserve the part. But then the part needs to be issued to make it disappear from the stock.
I have found a table that contains information about the part and the bill of material, it has a column named "QTY ISSUED" I have tried via an update command to set the qty issued = 1 for the reserved part. The table is updated, and via SQL everything looks fine, but the part doesn't disappear from the stock. So my guess is that there is a trigger or function somewhere that I need for this.
View 13 Replies
View Related
