SQL & PL/SQL :: Same Logic When AFTER INSERT OR UPDATE Trigger Is Written
Feb 19, 2011
SQL> CREATE OR REPLACE TRIGGER TRI_COMPL_FEATURES
2 AFTER INSERT OR UPDATE ON COMPLEMENTS FOR EACH ROW
3 DECLARE
4 v_fno NUMBER;
5 v_tab VARCHAR2(30);
6 v_unique_id VARCHAR2(40);
[code]....
Trigger created.When I am trying to insert into complements table it is throwing error as follows:
SQL> insert into complements values(19,NULL,'5',6,7,NULL,'W2023648',NULL,NULL);
insert into complements values(19,NULL,'5',6,7,NULL,'W2023648',NULL,NULL)
*
ERROR at line 1:
ORA-20010: ORA-04091: table TEEMNGWS.COMPLEMENTS is mutating, trigger/function
may not see it
ORA-06512: at "TEEMNGWS.TRI_COMPL_FEATURES", line 19
ORA-04088: error during execution of trigger 'TEEMNGWS.TRI_COMPL_FEATURES'
I can understand that I am trying to perform DML operation(i.e select) on table which trigger is fired.But how can I implement the same logic when AFTER INSERT OR UPDATE trigger is written.
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?
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 have to write a trigger where when the table is updated then one column named 'Status' should be updated as 'U' and if arow is inserted in the table then the column 'Status' needs to be inserted with value 'I'.
New to triggers....how to go with both insert and update conditions together.
Can we write a trigger which takes care of both insert and update. I have used Merge statement where I can write conditions based on insert/update done.
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.
I just want to know whether can i create a trigger where the sysdate can be automatically written into a table? for ex: if to_char(sysdate,'HH24:MI:SS') = 18:00:00 then it should be written in a table..?? is there anyother way instead of triggers??
I am trying to write a trigger that will do an insert/delete/update into a audit table when a change has occurred on the primary table. The change will be recorded in the audit table by a incemental sequence number and the updated data.
there will be an extra column in the audit table
how to get a simplified version of this trigger.
the primary table will look like so Id_num varchar (20) code Integer desc varchar(20) sequence_num Integer
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?
i have three tables: ot_req, ot_po_breakup, and ot_po. when a row is inserted into the ot_po table reffering to ot_req,there is a trigger which creates a seperate reocrd in ot_po_breakup with the details of ot_req ot_req.ri_sys_id as pb_ri_sys_id and ot_req.ri_qty as pb_ri_qty ,pi_sys_id as pb_pi_sys_id .upto this part is okand when i insert also the logic is okay.
i have created a trigger to update the rows in the ot_po_breakup table after insert on ot_poin order of pb_ri_sys_id and pb_pi_sys_id and try to update the values in the columns pb_ves1q, pb_ves2q, and pb_ves3q order. i am trying to take the quantities in ot_po.pi_qty and insert them into the pb_ves1q, pb_ves2q, and pb_ves3q columns of ot_po_breakup where those columns are empty (0 or null) such that the sum of those three columns for that row does not exceed the pb_ri_qty in that row.
My problem is i need to mofify my trigger to do the update and delete , that is whenever the user is updating the column ofot_po.pi_qtythe qty should in ot_po_breakup should get updated accordingly and sum of pb_ves1q,pb_Ves2q and pb_ves3q should be equal to pb_ri_qty and do the same for delete as well
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.
CREATE table ALPHA ( ID NUMBER, FK_NR NUMBER, INPUT NUMBER, OUTPUT NUMBER, MY_COUNT NUMBER, ACTUAL_TOTAL NUMBER );
I would like to record new inputs or outputs andthe column MY_COUNT should display the actual count of records for INPUT (actually MOD(INPUT, 10) ) regarding FK_NR and ACTUAL_TOTAL should display the actual sum for all input/output for FK_NR.
--ID normally comes from a sequence and BEFORE INSERT TRIGGER
EXAMPLE INSERT INTO ALPHA(FK_NR, INPUT, OUTPUT) VALUES (1 ,10,NULL); INSERT INTO ALPHA(FK_NR, INPUT, OUTPUT) VALUES (1 ,10, 50);
I must to build triggers that insert other two rows when the user insert the first record, but the First record must to change Seq to 2 (was 1) then in trigger to insert other record with seq equal 1 and more other record with seq equal 800, I tried some ways , but return error
END ARCTR_ACAO_IMEDIATA_IUreturn me error ORA-06519: active autonomous transaction detected and rolled back ORA-06512: at "CLIBGF.ARCTR_ACAO_IMEDIATA_IU", line 221 ORA-04088: error during execution of trigger 'CLIBGF.ARCTR_ACAO_IMEDIATA_IU' ORA-06512: at line 7using 9.2.08
I'm trying to insert data in my_second_table using a trigger and a view when I insert the data in my_first_table but there's something wrong in the code.
CREATE OR REPLACE TRIGGER my_trigger AFTER INSERT ON my_first_table FOR EACH ROW BEGIN
[Code]...
I'm suspecting that the problem is in the :new.cod_emp
P.S.: I'm using: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production on Enterprise Linux Server release 5.5 (Carthage)
I have one table with one row with some coefficients. If I update a coefficient from my table I want to be updated all the rows from another table. I've made a trigger but in the second table I have a column for example TOTAL and I want that column to be multiplied with my new coefficient, and I want this for every row in the second table. How I can select that total column in the trigger for every single row and update it?
I have make a new trigger.Create a trigger that inserting a new job_id MAX_SALARY assigned as the employee's salary more than 80 departmental charges
I have that code, is that correct?
CREATE OR REPLACE TRIGGER TR27 AFTER INSERT ON JOBS FOR EACH ROW BEGIN (SELECT MAX(SALARY) FROM EMPLOYEES WHERE DEPARMENT_ID=80); :NEW.MAX_SALARY := :OLD.MAX_SALARY; END;
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.
I must create an INSERT trigger, on an Oracle table, which will do an insert into my MS-SQL 2000 DB table.
The tables are exactly the same in this case and I desire to insert the entire row that was just insterted into the Oracle table into the MS-SQL table.
I understand how to create an ODBC connection between the DB servers, I just can't seem to understand the trigger syntax.
SQL> CREATE OR REPLACE TRIGGER TRI_ABOVE_JOINTBOX 2 BEFORE UPDATE ON JOINT_BOX FOR EACH ROW 3 DECLARE 4 PRAGMA autonomous_transaction; 5 BEGIN 6 IF (:NEW.SCALE_X <> :OLD.SCALE_X) OR 7 (:NEW.SCALE_Y <> :OLD.SCALE_Y) THEN
[code]....
The above code is for the GIS project. When I am trying to implement the above trigger it is giving output in such a way that the joint box(which is point feature in the designed database) scale is fixed to 1 as written in the code,but it cannot be moved in the DGN(front end),this is because trigger is fired before update.
Actual intention is that the feature(joint box) need to move in the DGN then the trigger need to be fired so that then scale need to fixed to one even after changing.For that I implemented after update trigger in the above code,but then it is throwing error as
ORA-04084: cannot change NEW values for this trigger type. I guess this is because after update trigger cannot be implemented for bind variables old and new.
1.joint box can move in DGN(this can be acheived automatically if after implementing after update trigger).
2.after dragging in the DGN the scale to be fixed as 1.
I am ceating a trigger to...when I update or insert a record in table and if salary of that record is less than 1600 than it should be updated to 2000. My trigger is
CREATE or REPLACE TRIGGER myTrigger BEFORE UPDATE OR INSERT ON newemp FOR EACH ROW BEGIN
[code]...
There are no errors with trigger while compiling, but when I update or insert a record, it does not update the table. It looks like the trigger is being ignored.
I have 2 tables where the data is related.table ot_pack_list,ot_delv .ot_pack_list contains details of items information that are packed to be delivered sometimes what happens user may remove some items from this, and the summary column of dl_wt and dl_qty in ot_delv must get automatically changed whenever there is addition in ot_pack.
I am writing a trigger TR_EMP on a table EMP which has columns EMP_ID, EMP_NAME, ALT_EMP_ID.
Now I am updating ALT_EMP_ID for an EMP_ID(PK) which is unique.
If ALT_EMP_ID is null then :new.ALT_EMP_ID = l_alt_emp_id; end if;
As this ALT_EMP_ID is unique, same ID shouldn't be inserted again here. When data being inserted with 2 different sessions for 2 different EMP_ID there is a possible chance of inserting same ALT_EMP_ID for both which results in Unique error. I need to handle this exception. DUP_VAL_ON_INDEX or OTHERS Execption not able to handle this.
I need a trigger to fire whenever an update happens. Seems easy, but there are several problems there:
- I need it to fire in case both :OLD and :NEW are the same value. i.e. I've got "5" currently in the field, and I update and set the same field to "5" again.
I have a table which contains 2 main columns apart from other columns. These two columns are REC_ID and ENTRY_DATE_TIME.
In this table data is inserted through a data load process using SQL Loader. REC_ID is populated to table through a sequence and ENTRY_DATE_TIME is populated as a sysdate.
select rec_id, to_char(entry_date_time, 'dd-mon-yyyy hh24:mi:ss') entry_date_time from itpcs_grt_oa_tran_rdpk_stock order by 1;
If we see the data, then time for REC_ID 4 is less than REC_ID 3.
Now the question is if REC_ID is populated through a sequence then sequence 3 must have been created before REC_ID 4 and if so then why the ENRTY_DATE_TIME of REC_ID 4 is less than REC_ID 3.
Further to the question, how data is written in tables. What steps are performed by at database level when we any DML(insert) is performed.
My DB Version : Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Step# 2 manipulated the tables: Inserted 3 records in Table_C.
Then I created an Insert Trigger to Table_A with an insert statement into Table_B and a subquery to Table_C. Please see below:
CREATE OR REPLACE TRIGGER TABLE_A_TR after INSERT OR UPDATE OR DELETE ON TABLE_A FOR EACH ROW DECLARE [code]......
Step# 3 compiled the created trigger and I've successfully compiled it. Step# 4 Tested the trigger (TABLE_A_TR) using an insert statement to TABLE_A. Insert into TABLE_A values (1,'testa','testb')
I've successfully insert the values into TABLE_A however I've observed that the trigger didn't execute the insert statement because TABLE_B has an empty rows. I tried to manually execute the insert statement just to see if there's an issue in my insert statement but I've successfully populated the values into TABLE_B. So I'm wondering why the trigger didn't execute the insert statement.
In the attached PRE-INSERT Trigger Code i need to do a validation. Validatation: If the Date range is between FEB 6 2013 and MARCH 1 2013 then the code should work ELSE It should throw a message
Check_Product_Title; Begin if :prod2.internet_product_flag = 'Y' and :prod2.brnd_code is null then soft_messages('E',TRUE,'Brand code must be specified for CCH online product'); end if;
