SQL & PL/SQL :: Create 2 Triggers And Each Trigger Will Call Only One Procedure
Feb 14, 2011
What is more efficient?
- To create one trigger on the table and make it call two unrelated procedures
OR
- To create 2 triggers and each trigger will call only one procedure.
I am trying to call procedure inside trigger.. but i get error ora-04098 ..
create table emp_hstry as select * from emp where 1= 2 ;
create or replace procedure emp_del_hstry(v_empno NUMBER , v_ename VARCHAR2, v_job VARCHAR2) is insert into emp_hstry (empno,ename,job) values (v_empno,v_ename,v_job); COMMIT; end;
create or replace trigger emp_del_hstry1 after insert or delete on emp for each row begin if deleting then emp_del_hstry(:old.empno,:old.ename,:old.job); end if; end;
delete from emp where empno = '7369'
AFTER delete statement run i get ora-04098 message i also check show error command ,but still i am not getting solution of this error ..
I'm trying to create a trigger and procedure to go along with my DDL. Everything is created just fine, but when I try to execute an update on the table monitored by the trigger I get the following error:
update housing set group_num = 1 where room_num = 10
ERROR at line 1: ORA-00937: not a single-group group function ORA-06512: at "YANKEEFAN146.VIOLATION_CHK", line 6 ORA-04088: error during execution of trigger 'YANKEEFAN146.VIOLATION_CHK'
/* DDL */ create table violation_type (violation_num number(1) primary key, violation_def varchar2(100) not null) organization index tablespace mi257_data; [code]....
I would like to create a trigger that will execute a stored procedure when a package/function/procedure is compiled. I tried creating an update trigger on user_objects, but it statues aI cannot create that trigger tyoe on views.
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.
ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.
now i can log in but here i faced with another erorr, while i am creating a trigger it gives the following erorr:
SQL> ed Wrote file afiedt.buf 1 create or replace trigger trgr 2 before insert on myTable 3 for each row 4 begin 5 select mySequence.nextval into :new.ID from dual; 6* end; SQL> / create or replace trigger trgr * ERROR at line 1:ORA-04089: cannot create triggers on objects owned by SYS
note: i tried and gave different names for the trigger, still it is the same.
I need to fire the KEY-DUPREC trigger as I click on the key combination Shift+P therefore; I added the following line of code in the frmweb.res file.
80 : 1 : "Shift+P" : 64 : "Duplicate Record"
It worked iff no error was raised. So, if I have a raise form trigger failure in the KEY-DUPREC trigger, a capital P will appear in the text field that called the trigger.
in a certain procedure I'm trying to call a procedure from another package in the same Schema. Package-name: Haku_Hops, procedure-name: veto and submitted is a parameter called opnum. So the following brings no problem:
Hops_Haku.veto(opnum);
But what if in the beginning of the package body I create the following variable: hak_pah := 'Hops_Haku.'; Is it then in anyway possible to call this other procedure through this variable, like e.g. hak_pah||veto(opnum);?
Until now I've only gotten error, even after declaring the variable in the declaration part.
I have a procedure(used to delete the data from all tables) which taking two parameters as input. like EXCS_kiwldate(1,'keepitems')[/color][/size] but it taking only one parameter at a time if i want to delete all data using that procedure by calling it in a cursor
how to write a cursor by giving loop function to delete the data once.
We have a stored procedure named proc_java_test . From this stored proc, we need to call a java class named java_test.java
This class posts messages to a queue hosted on an app server running on a separate box.
create or replace PROCEDURE PROC_JAVA_TEST ( num IN NUMBER ) AS LANGUAGE JAVA NAME 'java_test.post(int) ';
Is it mandatory to place java files in oracle_home/bin ? I tried placing the file on some other location and then calling it from stored proc. It did not work. Can we point oracle to read java file from location other than oracle_home/bin?
I have function in stored procedure which call function from another .net dll, is a first step. And this dll call function from unmanaged dll, is a second step.I do not have any problem in the first step, but when i do second step i have error:
ORA-20100: System.Security.SecurityException System.Security.Permission.SecurityPermission at ... at ... ORA-06512: at "SYS.DBMS_CLR", line 243 ORA-06512: ... ORA-06512: ...
when i am deploying proceduse i set security levels "external".
I need to call the VB function below from a Procedure's PL/SQL code and capture the returned variable into a varchar2 variable.I looked at the several means and nothing seems to work.
i want to insert unique number with out gaps in a col of a table every time my query runs . it shouel generate numbers from 1 to n. I can do it using sequence , but sequence need reseting and other user can reset it also and it creats gaps too.
Any function/technique which can give me session or transaction specific unique number on each call to sp.
i want to inserting these numbers in a table and insertion is going on in a loop.