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
create table my_rows ( my_envvarchar2(100), anumber(2), bnumber(2) ) / insert into my_rows values ('A', 10, 20); insert into my_rows values ('A', 10, 20); insert into my_rows values ('A', 10, 20); insert into my_rows values ('A', 10, 20); insert into my_rows values ('A', 10, 20); insert into my_rows values ('A', 10, 20); insert into my_rows values ('A', 10, 20); insert into my_rows values ('A', 10, 20); [code]....
The first row means that the value 10 represents 40% in the couple (10,20). Meaning if I have 100 rows with the couple (10,20), 40 rows will be marked with the value 10 and 60 will be marked with the value 20. To do this, I used to create a temporary table with the same structure as the my_rows table with a new column "the_value" and I used to update this new column wth a PL/SQL for loop. But I think it is doable in a signle SQL.
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.
i have created a trigger on a table after update. i am using if condition if the condition is true i am passing a value and then inserting into audit table.
if(condtion) L_change_type='value' end if; if(condtion) L_change_type='value' end if; if(condtion) L_change_type='value' end if; if(condtion) L_change_type='value' end if;
Then i am inserting
Insert into audit_table(change_type.....) values(..L_change_type)...)
If i want to skip the insert statement for particular condition wt i have to do.
create or replace view test_view as select 1 id,CONTRACT_REF_NO,chg_gl from test union all
[code]...
11 rows selected.
I have done only for one id.
so the problem is the front end is showing this test_view that people will modify.but they have to actually modify the table ,so I can think of an instead of trigger(instead of insert and instead of update ones).
I want to know how to create tree structure in forms6i. This should be created as " Control-block". For example when user expand department it shows all department names in college as sub tree. Note: I have no table for department..Just display manually..
Using oracle 11.2.0.3 and wish to do the following.
Have trigger on table a on database a.
When a record is inserted into table a a record should be written first to table1 on database b , committed then a record written to table2 on database b as table2 has fk dependency on table 1.
How can we best achieve this.
Getting problems with the commit inside the trigger have tried pragma autonous transaction as well.
Get ora-02064 distributed transaction not supported when use the following code
create or replace trigger por_TRG_POP_IKNOW_MULTIPLE2
after INSERT OR UPDATE or delete on por_MULTIPLE
for each row
-- tO ENSURE I-KNOW KEPT UP-TO-DATE WITH HOMIS MULTIPLE -- delib not delet form i-know as if has been used by customer cannot delete -- due to referential inetgrity constraints in i-know -- Note parent_num updated on IKW via support.maintain_latest_dim_version.update_multiple until -- When IKW becomes master of multiple info, this trigger can be removed -- and support.maintain_latest_dim_version.update_multiple removed
declare PRAGMA AUTONOMOUS_TRANSACTION; v_dml_type varchar2(1); begin IF INSERTING OR UPDATING
[Code]....
--when matched THEN update set -- a.MGRP_NUM b.mult_link_multiple_num, -- MGRP_DESCRIPTION = (select mult_name from por_multiple -- where mult_code = mult_link_multiple_code
I'm trying to create a trigger so that whenever a record in the Employee table is deleted, a trigger will automatically delete corresponding records in the Job History table, then the Employee record is archived to EmployeeArchive before it is deleted. It compiles but with warnings. Here's what I've got.
I would like to create a trigger on a table which populates a log table. In addition to using the table where the trigger will exist, I would like to populate a couple more fields in the log table with with data from 2 other tables.
e.g.
NAME_TABLE -reg_id -name
ADDRESS_TABLE *trigger to be fired when a new record is created here. -reg_id -srv_id
PROCESS_TABLE -srv_id -start_time -end_time
This is what I would like the logging table to look like:
create table test123 as (unit varchar2(5),qty varchar2(25)); insert into test123('ABC','10,40,50'); insert into test123('PQR','20,30,40,10'); insert into test123('XYZ','20,10,70');
I have a table called test123 which qty field. if the sum of qty is entered more than 100 or less than 100, it should throw error.
I wrote this trigger..but it is not working.
create or replace restrict_sum after insert or update of qty on test123 for each row declare v_sum number; [code].........
Today I’m trying to make a “table” from a column. The information I’m using looks like:
AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ - K
I need my table have these columns:
Column1 Column2 Column3 AAA B CCC DDD E FFF GGG H III JJJ K
So I began to divide the string into smaller pieces:
SELECT LEVEL reg, REGEXP_SUBSTR('AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ-K','[^|]+', 1, LEVEL) ferroc FROM DUAL CONNECT BY LEVEL <= LENGTH('AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ-K') - LENGTH(REPLACE('AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ–K','|')) + 1; What I get is: >
I have data that i am sorting, the data is mostly numeric (format of XXX-1234). is there any way to have my query sort xxx-1000 AFTER xxx-999? right now i am thinking i will have to create a separate sort order column on my table.
--this for txn details CREATE TABLE txn_det( txnid NUMBER PRIMARY KEY, amount NUMBER, status varchar2(50), cust_id NUMBER); ----this for customer details CREATE TABLE cust_det( cust_id NUMBER PRIMARY KEY, cust_name VARCHAR2(50), cust_acc number(15));
--data to insert for customer table INSERT INTO cust_det VALUES(101,'Miller','12345');
INSERT INTO cust_det VALUES(201,'Scott','45678'); ----data to insert for txn table INSERT INTO txn_det VALUES('tx0045',123.00,'success',101);
INSERT INTO txn_det VALUES('tx0046',4512.50,'success',101);
insert into txn_det values('tx0049',78.12,'success',101);
INSERT INTO txn_det VALUES('tx0055',123.12,'success',201);
Now THE problem IS cust_det TABLE's cust_id coulmn may contain duplicate.So I thought OF adding THE txn_id COLUMN TO THE cust_det table but I know that encourgaes redundancy.
How to take table structure in oracle? Actually I got it through this command "SELECT dbms_metadata.get_ddl(a.object_type,a.object_name) FROM user_objects a where object_type != 'PACKAGE BODY'"
any other way to get it? I need like table name field name datatype
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; /
I am receiving two large export files from a vendor, so I have no control over the contents. I need to import these into our database. The two export files are very similar, except the one has slightly differenet columns in it. So, export file 1 may have a table:
COLUMN_A COLUMN_B COLUMN_C
The second file may have:
COLUMN_A COLUMN_B COLUMN_D
At the destination, I have a table that has:
COLUMN_A COLUMN_B COLUMN_C COLUMN_D
Is there a parameter that would let me interchangably import either (or both) files into this destination table? This is my first attempt at data pump - but I know using import this has caused me issues. Not sure if the same limitations exist? Will the missing columns cause it to fail?
