Compound Triggers Not Firing
Nov 1, 2011
I have rather large compound triggers that I discovered were not firing this morning, so I created a simpler compound trigger to test:
CODECREATE OR REPLACE TRIGGER "test"
FOR INSERT OR DELETE OR UPDATE OF KI_NM ON CHEMAXON.CB1ASSAYS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
ENABLE
[code]...
It's just not firing. The tables are all in the owner's schema (who has DBA rights). My Google-fu is failing me, and I'm not sure how to start troubleshooting general trigger failure.
View 2 Replies
ADVERTISEMENT
Jan 25, 2012
After many tests I can't make work and update of the same table inside the same table.
Trying to avoid Mutating Table Error now I have
ORA-00036: maximum number of recursive SQL levels (50) exceeded
Sample Data :
create table test_compound (USERID VARCHAR2(10),APP VARCHAR2(15),LAST_UPDATED_ON TIMESTAMP);
insert into test_compound values ('user1','1',systimestamp);
insert into test_compound values ('user2','2',systimestamp-4);
insert into test_compound values ('user3','3',systimestamp-6);
CREATE OR REPLACE TRIGGER trigger_test
FOR UPDATE ON test_compound
COMPOUND TRIGGER
TYPE t_tab IS TABLE OF VARCHAR2(50);
l_tab t_tab := t_tab();
[code].......
When I execute :
update test_compound
set last_updated_on=systimestamp
where userid='user1' and app='1';
The trigger should update the first row and all the data from test_compound table where userid='user1'. Maybe the problem is that updating the same table inside the trigger is firing in a recursive way the trigger.
View 13 Replies
View Related
Jun 23, 2011
I have a table T_TEST, i will update the table rows, i want to add a single record in some other table let say T_A, which contains the sum of the updated difference of all the records.
let say, if i have T_TEST with values of 100, 200 and if i update it to 500, 600 (oldvalue+200) then my T_A should contain a record which is 400.
I was able to implement the solution in Oracle 11.2.0.1 using compound triggers. I would like to know how to do it in earlier versions of oracle or Is ther any other way to do it.
create table t_test ( a number);
begin
insert into t_test values (200);
insert into t_test values (200);
insert into t_test values (200);
insert into t_test values (200);
[code]...
View 12 Replies
View Related
Aug 7, 2012
Is it possible to use a compound condition on a form listing say 15 records utilizing the F11 key?(ie. order column <10 and >5)
View 2 Replies
View Related
Jun 9, 2010
I have a database trigger that fire on delete or update. It works fine. But after few days, only delete is working. When I update, nothing is happening. Then I re-create the trigger and it works fine. Then the problem comes back after few days.
The code is for both action so I wonder why this happens:
AFTER
DELETE OR UPDATE
ON AGENCY.CCS_TEMPLATE REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
I checked the trigger on all_objects table and it is valid. On schema browser it is compiled.
View 1 Replies
View Related
Apr 30, 2013
I'm trying to find some information on the performance impact of a trigger on a heavily updated table when the condition to fire the update trigger is NOT met. In other words I guess what I'm really trying to find out is what the performance impact of the system checking the condition on the trigger to determine if it should fire or not is.
For example I have a batch job that inserts and updates a table heavily, but the batch job almost never updates the column in question on the trigger to the value that would cause it to fire, but it does update that column to other values often.
I know about the many downsides of using triggers in general, but I'm working with a third party application, so more optimal solutions aren't an option.
View 1 Replies
View Related
Nov 5, 2012
I am navigating from a Master form to a child form. The when-create-trigger in the child form is sometimes executing and sometimes not. Not firing of this trigger is causing the child form to open without any initially assigned values. What is the root cause and also the scenarios where the when-create-trigger fires.
View 15 Replies
View Related
Oct 22, 2011
Our Team is planning to find a new architecture for our new project. In which we have to fire query to multiple database and then we have to collect all responses from them.(Suppose there are 10databases on which we have to fire query)
I searched a lot,the only thing I got is...It could be possible only through Database link(DbLink),Is there any other way to fire query on distributed databases...?
View 20 Replies
View Related
Feb 4, 2011
How to find the tables in the database on which high DMLs are firing.
View 5 Replies
View Related
Mar 8, 2013
I have created custom table in inventory and after register table & columns, i create event alert on it. event alert is not firing.
View 1 Replies
View Related
Dec 26, 2011
What is the purpose and use of 'Instead of Triggers'
View 20 Replies
View Related
Dec 4, 2010
I have to write two triggers on same table for auditing different columns of different users(may be different modules).
I will have an audit table in which i will insert data such as (user_id,module_id,column_name,old_col_val,new_col_val,timestamp)
Now different users from different modules will update the data on same table may be same columns from different front end forms!
If we write directly, we will not be able to know which column is updated by which user.
My question is in this case how can we control the triggers to raise differently?
View 3 Replies
View Related
Jul 1, 2010
Im going to create a trigger(insert or update) for a table A, when it fires inserted or updated columns are should be insert or update into another table say table B, one column(Processed_time) in table B where its value will be by subtracting two columns in table A(response_time,Submission_time) all are timestamp type.
How to update(Processed_time) in table B by subtracting above two columns in table A.
View 2 Replies
View Related
Dec 3, 2010
The trace directory is full of trc files with the text below:
--------Dumping Sorted Master Trigger List --------
Trigger Owner : INPUT
Trigger Name : CUS_TST
--------Dumping Trigger Sublists --------
There is like a file generated every minute, and i cant stop it from happening.I have tried setting the trace_enabled parameter to FALSE but no success.
View 2 Replies
View Related
Jul 26, 2004
I have column names stored in a table(say t1).
Inside a trigger I have to dynamically get the : old. values for only these columns.
This I need because during update trigger on a table (say t2) I am supposed pickup the coumn names stored in t1 and get the : old values of only these columns to insert into a new table .
View 5 Replies
View Related
Oct 26, 2010
I am creating triggers for audit operation when any insert/update/delete happens on a table automatically.Now i need the same functionality in procedure.
My requirement is to create procedure for all those triggers.
View 2 Replies
View Related
Jul 18, 2013
There are two servers A and B ,and i am maintaining one table suppose table1 and another table suppose table2 , DML triggers are made for both of these tables.The action of DML trigger is that the movement we are inserting values , suppose a table1 on server A so automatically the same set of value in table2 on server Bso my problem is that the movement i inserted value on table1 on server A ,
the same set of values were inserted into table2 of server B and as the values were inserted intotable2 , the same set of values were inserted into the tabld1 because of the trigger. How to stop the non ending loop which occurs due to the simultaneous firing of triggers and insertion of same records into both the table endlessly...
version :---Personal Oracle Database 10g Release 10.2.0.3.0 TNS for 32-bit Windows: Version 10.2.0.3.0
View 0 Replies
View Related
Jan 11, 2011
I'm having a bit of a problem getting the syntax of a trigger right. Unfortunately, I have no DBAs locally, I use some third party software, and for reasons beyond my understanding, I have been told to use triggers, and not stored procedures, so I'm running with it.
The set up:
STRUCTURES table: contains several columns, one of which is the unique ID column.
ASSAY table: contains several rows, also with the same ID column, but can have more than one row per ID (several assays per compound). One column is XC_ASSAYS.
The idea of the trigger is basically: When a row in the ASSAYS table is updated, pull out the ID of the row, then calculate the average of the XC_ASSAYS columns for those rows, and report it to the STRUCTURES.XC_ASSAY column for that row ID.
My best attempt thus far results in compilation errors.
CREATE TRIGGER INHIB_W_ALA_TR
AFTER INSERT OR UPDATE ON ASSAYS
FOR EACH ROW
BEGIN
UPDATE STRUCTURES SET XC_ASSAY = (SELECT AVG(XC_ASSAY) FROM ASSAYS WHERE ASSAYS.ID = :NEW.ID) WHERE STRUCTURES.ID = :NEW.ID
END;
/
The resulting errors are:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PL/SQL: SQL Statement ignored
2/190 PL/SQL: ORA-00933: SQL command not properly ended
12/0 PLS-00103: Encountered the symbol "end-of-file" when expecting
[code]...
I don't understand some of the errors, such as why line 2 SQL is ignored (it seems correct?), or I'm supposed to properly terminate the trigger (I've read ; and /, but I'm getting the end-of-file errors when I do so). Tried shuffling syntax and ' or " around - and I can't get it.The body SQL works when I replace :NEW.ID with an actual variable (such as 'NMP12'), but I'm not sure how to pass the ID variable from the updated row into the body. The ID is not updated, but other columns are.
View 3 Replies
View Related
Apr 29, 2013
I have a question about GG Sequence Replication and Triggers. My main database, which I would like to replicate on another server, is highly dependent on sequences for assigning surrogate keys to every row in every table in the application. I know that I need to add Sequence support to my source database (plus supplemental logging, etc), but I'm curious about the target database.
I do not anticipate allowing Read/Write access to this database - we are migrating from 10.2.0.4 (source) to 11.2.0.3 (target) on a new platform, and I want to keep the 11g database up-to-date with our production data until it is time to begin the actual conversion of our application. My thinking is that if I use the SUPPRESSTRIGGERS dboption in my Replicat session, this should take care of the use of the Sequences for assigning the surrogate key values, and the data should add to the tables normally without any intervention by the sequences/trigger combination. I know I will have to manually "correct" the sequences on my 11.2.0.3 database whenever I want to open this database up for use, but I have a script for this ready to go.
Also, in my source database, I am using Oracle Context indexes for generic name searching - this feature creates a number of DR$ named tables in the main application schema that I am replicating (approximately 50 of them). I am assuming that I should EXCLUDE these tables from the replication, as the context indexing should automatically update them as changes to the underlying data are applied via the replication of the indexed tables.
View 1 Replies
View Related
Sep 5, 2010
why when-mouse move,up,enter,leave are not working in oracle forms 10g except when-mouse-click and when-mouse-doubleclick on any items.
what i need to do to execute this triggers..
View 3 Replies
View Related
Feb 24, 2010
I am unable to understand why row level triggers cant be used in mutating tables.
If you need to update a mutating table, you could bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers--an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
View 1 Replies
View Related
Dec 12, 2012
can change the order of triggers in forms 6i . that means firing in form level?
View 4 Replies
View Related
Dec 23, 2011
How to catch or store the sql statement that caused the trigger to fire in DMl Triggers.
View 11 Replies
View Related
Oct 24, 2013
I am working on a form, whose one block is query_find, so on clicking find button it will open another block.the find block contain 4 column and second block contain around 10 column.but the column present in find block are associated to standard tables and the column present in block-2 are from custom tables.there is a query which link those column.
I am not sure where to use that query, so that it will take input from the find block and then populate the result into the block-2.
View 13 Replies
View Related
Jan 18, 2006
Suppose if I have defined two triggers Key-next and When-validate-item on the same item.
If my key-next is going to fire first and When-validate-item laterhow can the validation happen if I write a code like go_item('XXXX') in my Key-next.Does it mean that if u write a code in key-next to jump to some other item, no validation takes pLace?
View 14 Replies
View Related
Dec 5, 2012
Some triggers are dropped and stored in recycle bin. when i am trying to restore it by the command
1* alter trigger "BIN$FFRO1R1LSuSIZ6uyLocD6g==$0" rename to WFNOTIFICATION_GEN_PK
SQL> /
alter trigger "BIN$FFRO1R1LSuSIZ6uyLocD6g==$0" rename to WFNOTIFICATION_GEN_PK
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
View 13 Replies
View Related
Jul 14, 2012
How can we overcome recursive triggers in oracle SQL?
View 7 Replies
View Related
Sep 9, 2012
i've a problem regarding my code at line: 76. URL....
If i put a RAISE_APPLICATION_ERROR just before:
SELECT ID_GIOCATORE INTO CONTR_GIOCATORE2
FROM COMP_CONTR_GIOCATORE_PARTITA GC JOIN CONTRATTO C ON GC.ID_CONTRATTO = C.ID_CONTRATTO
WHERE GC.ID_PARTITA = :NEW.ID_PARTITA AND (C.ID_CASELLA = 28 OR C.ID_CASELLA = 12) AND C.ID_CASELLA <> :NEW.ID_CASELLA AND
[code]....
But if i put the RAISE_APPLICATION_ERROR just after this statement and before IF(CONTR_GIOCATORE2 IS NOT NULL AND CONTR_GIOCATORE2 = CONTR_GIOCATORE) THEN, nothing happens after the insert(that goes well) and the trigger doesn't do his job(insert,update etc). if i do that select, i got the no data found, so i put the exception to set the variable CONTR_GIOCATORE2 to NULL.
View 1 Replies
View Related
Mar 1, 2013
I recently stumbled upon a post for an undocumented golden gate procedure that I find VERY useful when repairing / simplifying spatial data as a DBA without causing user triggers to fire and do unnecessary work, etc. Anything with foo in the name must be good, right? ;-)
HINT: Oracle please make this a documented feature!
This will disable triggers – just for a session – no system wide changes or trigger mods needed! Yes, fire needs to be set to true - to disable the triggers from firing.
exec sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true);
Note, you will need execute permissions on sys.dbms_xstream_gg first.See for more info: URL.....
View 1 Replies
View Related
Jan 24, 2012
May i know the triggers in forms 6i, which is applicable to all items,blocks and form.
View 9 Replies
View Related
