Plsql trigger if updating

13 Sep

Nidhi create or replace trigger Trigmytest1 before insert on mytest1 referencing OLD as old NEW as new for each row begin if :new.col1 = 100 then" id="ctl00_m_m_i_ctl00_gr_ctl17_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1455205" / In that case won't it become mutating trigger because i will be updating in the trigger which will aging fire the trigger and so on.Whole point of having Automonus transactions will fail. statements in your procedures but if you just pass values back, you are setting values for extra columns in the row which you already updating/inserting, so trigger is not fired again thx" id="ctl00_m_m_i_ctl00_gr_ctl23_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1455236" / it will be mutating trigger if you have insert/update statements in your procedures but if you just pass values back, you are setting values for extra columns in the row which you already updating/inserting, so trigger is not fired again thx create or replace trigger TRBIU_Test Employees before insert or update on Employees referencing OLD as old NEW as new for each row begin :new.email := Case when :new.employee_id = 1000 then func1(1000) when :new.employee_id = 1002 then [email protected]' End; If :new.employee_id = 1001 then PROC2(:new.employee_id,:new.email); end if; end; where PROC2 is something like this CREATE OR REPLACE PROCEDURE PROC2 ( Emp_Id_IN IN VARCHAR2 , Email_OUT OUT VARCHAR2 ) AS BEGIN If Emp_Id_IN = 1001 then Email_OUT := [email protected]'; end if; END PROC2; Hope this would help to resolve the issue....Balaji Do use the WHEN clause for improved performance. create or replace trigger Trig On Test1 BEFORE insert or update on mytest1 referencing OLD as old NEW as new for each row WHEN (new.col1 = 100) begin :new.col3 = 500; end; Brian updating/inserting inside of these procedures, they should be returning values back to the trigger and then you populate these values into :new.column thx" id="ctl00_m_m_i_ctl00_gr_ctl13_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1455208" / you can call any number of procedure, but instead of updating/inserting inside of these procedures, they should be returning values back to the trigger and then you populate these values into :new.column thx create or replace trigger TRBIU_Test Employees before insert or update on Employees referencing OLD as old NEW as new for each row WHEN (new.employee_id = 1001) begin :new.email := [email protected]'; end; Hope this would help!create or replace trigger Trig On Test1 BEFORE insert or update on mytest1 referencing OLD as old NEW as new for each row WHEN (new.col1 = 100) begin :new.col3 = 500; end; Brian" id="ctl00_m_m_i_ctl00_gr_ctl09_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1455177" / oops, yes - Roman is right. Nidhi" id="ctl00_m_m_i_ctl00_gr_ctl15_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1455206" / you can use before insert or update trigger, something like this create or replace trigger TRBIU_Test Employees before insert or update on Employees referencing OLD as old NEW as new for each row WHEN (new.employee_id = 1001) begin :new.email := [email protected]'; end; Hope this would help!Here are the answers to the PL/SQL Challenge questions in last issue’s “Error Management” article: Answer 1: Choices (a), (c), and (d) all raise ORA-00001, also known as DUP_VAL_ON_INDEX inside PL/SQL code.Answer 2: To make it possible for the plch_proc procedure to compile without error, change “AND” to “OR” in the WHEN clause of the exception section.

Balaji" id="ctl00_m_m_i_ctl00_gr_ctl07_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1455176" / Change your trigger to before insert and instead of calling another procedure use the below if statement IF :new.col1 = 100 THEN :new.col3 = 300 END IF; Let me know why this solution won't work in your situation.

It might be better include the logic in the trigger itself." id="ctl00_m_m_i_ctl00_gr_ctl01_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1455169" / The procedure is an autonomous transaction.

create or replace trigger Trig On Test1 BEFORE -- before instead of after insert or update on mytest1 referencing OLD as old NEW as new for each row begin if :new.col1 = 100 then :new.col3 := 500; -- set value here end if; end;" id="ctl00_m_m_i_ctl00_gr_ctl03_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1455172" / you should do this: create or replace trigger Trig On Test1 BEFORE -- before instead of after insert or update on mytest1 referencing OLD as old NEW as new for each row begin if :new.col1 = 100 then :new.col3 := 500; -- set value here end if; end; it would be much easier to do it all in a single trigger.

I can even shorten things further and write CREATE PROCEDURE process_employee ( employee_id_in IN omag_employees.employee_id%TYPE) IS l_employee omag_employees%ROWTYPE; BEGIN SELECT * INTO l_employee FROM omag_employees WHERE employee_id = employee_id_in; END; The SELECT * syntax tells Oracle Database to fetch all the columns in the table.

I can also use %ROWTYPE to declare a record that has the same structure as a SELECT statement in a cursor.