Creating Trigger Through Procedure
Jul 24, 2009
I am trying to create trigger through Procedure due to following reasons-
1. The name of the column on which trigger will execute, is to be fetched dynamically.
2. The name of the column to be updated, is to be fetched dynamically.
Here is the sample code-
CREATE OR REPLACE PROCEDURE test2
IS
var VARCHAR2 (4000);
uname VARCHAR2 (30);
attribtask VARCHAR2 (100);
mapcol VARCHAR2 (100);
BEGIN
[code].........
On execution, the procedure throws the error of 'Insufficient privileges'. The 'var' seems to be the main culprit because the issue disappears if var is set to 'select * from dual'. Also, if i take the output (value of var) given by DBMS_output.put_line function and execute it explicitly, trigger gets created.
In addition- The procedure is (and being executed) within the same user/schema under which trigger is going to be created.
View 2 Replies
ADVERTISEMENT
Jul 19, 2011
I need to create a trigger for the below case:
index_id Time_vertex_id date rate pre_rate
4 1 17-06-2011 4.7 6.4
4 1 16-06-2011 6.4 7.4
4 1 14-06-2011 7.4
4 1 15-06-2011 8.4 7.4
the index_id and time_vertex_id will be unique and when the date is 17th i.e the first date will be inserted the current rate will be 4.7 and the previous rate will be blank and when another date is inserted i.e 16th the previous rate of 17th will be the current rate of 16th i.e 6.4.
when 14th is being inserted, the previous rate of 16th will be the current rate of 14th i.e 7.4 and if after 14th is being inserted, 15th is being inserted, then the previous rate of 16th should be updated as per the current rate of 15 say 8.5.
View 5 Replies
View Related
May 25, 2012
I would like to create a trigger on a table, but only if the table exists.
This is what i've got so far:
create or replace function tableExists (tablename in varchar2) return boolean
is
c int;
begin
[Code].....
This would give me the error:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update
View 1 Replies
View Related
Sep 21, 2008
I have a table tblcustomer and a view called vworder. I need to create a trigger such that any data being added into vworder first checks if the field customerid has the data in customerid of tblcustomer.. all it has to do is spit out a error "Customer ID not found"
So I created a view such as
Quote: create or replace TRIGGER trig_order
BEFORE INSERT OR UPDATE ON vworder
FOR EACH ROW
DECLARE
cust_id VARCHAR2(20);
BEGIN
SELECT customerid INTO cust_id FROM tblcustomer WHERE customerid = :new.customerid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Raise_application_error(-20000, 'Customer ID Does Not Exist');
END;
But it comes with error
Quote: ORA-25001: cannot create this trigger type on this type of view 25001. 00000 - "cannot create this trigger type on views" *Cause: Only INSTEAD OF triggers can be created on a view. *Action: Change the trigger type to INSTEAD OF.
I am not an expert at all and I need to finish this today itself.
View 7 Replies
View Related
Jan 4, 2007
Having trouble creating a trigger to populate another table.
The SQL:
CREATE OR REPLACE TRIGGER "P_M_YES"
AFTER INSERT OR UPDATE ON DOMAIN
REFERENCING NEW AS NEW.P_M AND OLD AS OLD.P_M
FOR EACH ROW
WHEN (NEW.P_M = YES)
BEGIN
INSERT INTO PAGE_MAKER VALUES(:NEW.D_NAME, :NEW.USER_ID);
END P_M_YES;
/
ALTER TRIGGER "P_M_YES" ENABLE
/
I get an invalid trigger specification.
View 3 Replies
View Related
Mar 22, 2007
I've been assigned a task at work that consists of creating a trigger on a table. This table is used to store temporary query results. I'm trying to make a trigger to clean the table so old results don't accumulate.
There is a column named DATE_UPDATED that stores an Oracle Date for when the row was inserted/updated. If the row is not updated in 12 hours, I want to delete it.
Here is what I have so far.
CREATE OR REPLACE TRIGGER clean_tableName
AFTER INSERT OR UPDATE
ON tableName
BEGIN
END clean_tableName;
As you can see, I don't have an actual function body yet. What would be the best way to accomplish this? Should I declare a variable to store the current Date+12 hours and then just compare that in a delete statement?
I'm thinking something like:
DECLARE
laterDate DATE := SYSDATE+12; // Not sure how to add 12 hours to a date.
BEGIN
DELETE FROM tableName WHERE DATE_UPDATED > laterDate;
END clean_tableName;
But I'm not sure how to add 12 hours to the current date. Is there a way I can do this without defining a variable?
View 2 Replies
View Related
Aug 14, 2011
I am new to PL/SQL and how to create a trigger to compute the population of the school from the groups of students and store back in population. It also needs to check that there is a min of 10 students to a school.
CREATE OR REPLACE TYPE group_type AS OBJECT
(
group_nameVARCHAR2(20),
tutor_idNUMBER(5),
[code]...
View 11 Replies
View Related
Apr 6, 2012
When i try to create a trigger , i ended up with error.
SQL> create or replace
2 TRIGGER LOGON_TRG AFTER LOGON ON DATABASE
3 BEGIN
4 INSERT
5 INTO
6 user_audit_log
7 SELECT
8 user#,
[code]....
Warning: Trigger created with compilation errors.
SQL>
SQL> show error
Errors for TRIGGER LOGON_TRG:
LINE/COL ERROR
-------- -----------------------------------------------------------
2/5 PL/SQL: SQL Statement ignored
17/17 PL/SQL: ORA-00942: table or view does not exist
The command used to resolve the error is
GRANT SELECT ON v_$session TO jack;
Jack user has sysdba privilege. My question is 'sysdba' is a super and special user which has all the privileges in database. Then why does it need SELECT privilege on v$session to user to create the trigger?
View 3 Replies
View Related
Aug 24, 2011
I know this is an old thread and I just started working with triggers. I want to check if the data being inserted is already in the table and if not insert it:
create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
values (:new.user_id, :new.location);
end if;
end;
what if I wanted to keep the user but only update location if the user is already in the table. I've tried doing it this way:
create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
[code]...
View 4 Replies
View Related
Sep 20, 2011
i need a trigger with alter commands to alter the table structure,it will be captured in a separate meta data table(META)
CREATE OR REPLACE TRIGGER meta_alter AFTER Alter ON SCHEMA
BEGIN
update meta set column_name=:new where table_name=ora_dict_obj_name column_name=:old;
END;
/
Meta table contains Table name and column name..i attached the table data in atext file
View 39 Replies
View Related
Mar 12, 2011
Recently decided to try using Oracle. Previously when creating a procedure in SQL server I would have done the following.
Create PROCEDURE GetInfo AS
SELECT InfoID, Name, Description
FROM GetInfo;
How can I write this same procedure in Oracle?
View 10 Replies
View Related
Mar 4, 2011
I have create a procedure as
create or replace procedure aa is
abc varchar2(1000);
begin
abc:='create or replace view abc_view as select * from abc';
execute immediate abc;
end;
When i try to execute the same as
BEGIN
aa;
END;
i got error as
ORA-01031: insufficient privileges
ORA-06512: at "EARS.AA", line 5
ORA-06512: at line 3
why i get this error. there is no problem in grants. the same create or replace view query works if i directly executes in sql.
View 8 Replies
View Related
Jun 28, 2012
I am getting many errors when creating the below procedure.
alter procedure sp_compileinvalid as
Begin
Set lines 999;
Spool run_invalid.sql
select
[code].......
View 14 Replies
View Related
Apr 19, 2012
My requirement is like as follows,
declare
v1str varchar2(100):='select empno,ename from emp';
v2str varchar2(100):='select empno,ename,sal from emp';
type t_array is varray(2) of varchar2(100);
[Code]....
So my problem is while executing the different sql statements by passing it to the procedure,how can the procedure would behave dynamically.It must be able to process all the sql statements.
View 21 Replies
View Related
Nov 19, 2013
I am writing this procedure with a explicit cursors defined in it. However when i compile the procedure i get this error: Error(39,1): PL/SQL: SQL Statement ignored Error(39,1):PLS-00394: wrong number of values in the INTO list of a FETCH statement .
create or replace PROCEDURE PRO_ICMISd_customer_no BB_PM.customer_no%type;d_pr_code_bbl BB_PM.pr_code_bbl%type;d_pr_code_pmm BB_PM.pr_code_pmm%type;d_subdept_desc PM.subdept_desc%type;d_class_desc PM.class_desc%type;d_cat_desc PM.cat_desc%type;d_subcat_desc PM.subcat_desc%type;d_brand_name PM.brand_name%type;d_product_desc PM.product_desc%type;d_unit_price_bbl PM.unit_price%type; e_customer_no BB_PM.customer_no%type;e_pr_code_bbl BB_PM.pr_code_bbl%type;e_pr_code_pmm BB_PM.pr_code_pmm%type;e_subdept_desc PM.subdept_desc%type;e_class_desc PM.class_desc%type;e_cat_desc PM.cat_desc%type;e_subcat_desc PM.subcat_desc%type;e_brand_name PM.brand_name%type;e_product_desc
[code]....
View 17 Replies
View Related
Mar 5, 2012
I have to create a stored procedure having some 10 cursors and i have to display the data's fetched by select statement in cursors using dbms_output.put_line().
for ex:
-------
CREATE OR REPLACE PROCEDURE PROC AS
CURSOR C1 AS SELECT * FROM EMP;
BEGIN
FOR R IN C1
LOOP
[code].....
My question is while creating procedure i want to check if the object exist in database or not.
If EXIST
NO PROBLEM MY CODE CREATE THE PROC AND EXECUTED FINE
IF NOT EXIST
I don't want an exception to be thrown stating "OBJECT DOES NOT EXIST" I wanted PL/SQL engine not to execute the particular select statement itself and continue executing other select statements.
The reason why have such kind of wierd requirement is my program displays all the CEMLI objects in any 11.5.10 instance. I don't know whether they have installed discoverer or not. if they installed it no problem else my program have to eliminate.EXECUTE IMMEDIATE, REF CURSOR becoz i tried it and works fine.but not able to wrap in WRAPPER UTILITY 8.0 versions.
View 14 Replies
View Related
Jan 23, 2012
I am trying to get my stored procedures together again after many years of working on MS SQL server. I'm alternating between using SQL-Plus and Oracle SQL Developer for Mac. In SQL Developer - which I like to use - I keep getting errors at the point where the END command for the package header occurs and the create command for the package body starts.
I've found that if I run the following in SQL-Plus I'm OK but if I try it from SQL Developer I get a compile error such as following or else an error telling me that it expects function, or pragma or something to that effect:
Error(8,1):PLS-00103:Encountered the symbol "/"
The code is as follows:
--------------------------------------
CREATE OR REPLACE PACKAGE MACR_SAMPLE_PROC_4 AS
PROCEDURE sampleproc_4(
pParam1 in integer,
pParam2 out varchar2);
END MACR_SAMPLE_PROC_4;
[code].....
View 9 Replies
View Related
Jun 6, 2012
I have a Table with 4 columns and I am creating a procedure with 4 input parameters and inserting into the table as 4 parameters and how to use loop to insert multiple records.The data to be taken from excel.please suggest how to use the loop to insert multiple records.
create or replace procedure PRC_add_data(
P_Emp_No varchar2,
P_Member_Name varchar2,
P_IDvarchar2,
P_UHID varchar2
)
is
BEGIN
INSERT INTO UploadData (Emp_No,Member_Name,ID,UHID) values (P_Emp_No,P_Member_Name,P_ID,P_UHID)
END;
/
View 6 Replies
View Related
Oct 23, 2012
I have created a procedure within a database package, but when I want to create a form based on procedure but I can't call it. I think that I have to use prefix, I am a beginner in database and I don't know how to do this.
View 4 Replies
View Related
Apr 8, 2012
I'm trying to create a store procedure that will accept a username from a flat file but i don't know how to do read file into store procedure.
Below is a sample store procedure by itself i created to add user which created okay but when i execute I got the error displayed below.
create or replace procedure addUsers(userNam in varchar2)
is
begin
EXECUTE IMMEDIATE 'CREATE USER'||userNam||'IDENTIFIED BY "pass1234" DEFAULT TABLESPACE USERS'||'QUOTA "1M" ON USERS'||
'PASSWORD EXPIRE';
end addUsers;
/
[code].....
View 21 Replies
View Related
Aug 20, 2010
I am writing a procedure that will be called from a java wrapper.
The procedure do a lot of data manipulations and in between i am creating global temp table and saving the data into it for each request thats given as a parameter to the procedure. After all the processing i have to write the data from this global temp table into a physical table and atlast drop the temp table.
Create or replace proc_name ()
update table........
delete from ..........
CREATE GLOBAL TEMPORARY TABLE TSAAG
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
)
insert into............
drop table TSAAG;
End;
creating a global temp table inside a procedure is expensive...
Do we have anything like creating table before and calling the instanse of it in procedure.
Do we have any alternatives to this..
View 3 Replies
View Related
Aug 26, 2013
I am in the process of creating a trigger for a procedure I created. The procedure is working fine but I keep getting an error on the trigger. I am getting Error(7,5): PL/SQL: Statement ignored AND Error PLS-00306: worng number of types of arguments in call to 'INSERTINTOPHYSLOG'
Here is my procedure:
create or replace PROCEDURE INSERTINTOPHYSLOG
AS
prec_num long;
srec_num long;
[Code]....
View 2 Replies
View Related
Sep 12, 2011
Moidfy trigger into procedure
I am quite a beginner One of trigerred jobs is freezing and I have to reproduce the issue. Thing is about extracion and export data from one table to another (from Rating to Xtexport_table_1).
I want to build query which will fill xtexport_table_1 with values, partialy declared and partialy fetched from Rating tabble.
I figured out that that I have to get rid of all condition statements(IF, WHEN etc ), and just declare variables for some columns and then use a SELECT statement to fetch data.
create or replace
TRIGGER TRG_RATING_EXP_DLS
BEFORE DELETE OR UPDATE OR INSERT
OF RATING_ID,EXTERNAL_ID_1,DN_RATING_ATTRIBUTE_ID,ALI GNMENT_ID,TEAM_ID,CUSTOMER_ID,AFFILIATION_ID,PRODU CT_ID,PERIOD_ID,VALUE_MIN,VALUE_MAX,TENANT_ID,USER _ACCOUNT_ID
ON RATING
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
[Code]...
-------------------
Don't play what's there, play what's not there
View 2 Replies
View Related
May 11, 2010
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]....
View 9 Replies
View Related
Jul 7, 2010
what is difference between in procedure and trigger?
View 2 Replies
View Related
Dec 8, 2006
I searched, found this one hit and according to mateoc15, you cannot commit within a procedure that is called from a trigger. He must be right, because mine is not committing either, nor are any errors given.
Trigger
Create or replace trigger owner_name.table1_trg2
after update on table1
for each row
call owner_name.procedure1;
procedure Code (psuedo):
Create or replace procedure1 as
begin
update table1 set col1 = 'whatever';
commit;
exception when others then
rollback;
Executing the procedure as owner_name on SQLPlus works fine, but when I update a column of the table via the PL/SQL package (on the web form), the column does not update, telling me that the procedure never fired from the trigger.
View 5 Replies
View Related
Jun 27, 2012
Recently I created a trigger in my production environment which affected a procedures execution.
Trigger code is as below
CREATE OR replace TRIGGER chk_fresh_lead_time
BEFORE INSERT ON location_refnum
REFERENCING NEW AS NEW
FOR EACH ROW
WHEN (NEW.location_refnum_qual_gid = 'FRESH_LEAD_TIME'
[code].......
This trigger will check if a value is inserted on Location ref num table for the qualifier LEAD_TIME only if the value exist in Fresh template table else it will throw an error message as Quote: Add the fresh template and then add the LEAD_TIME value
We have a procedure in one of the package which inserts values on the same Location ref num table but for a different qualifier say PENDING and not LEAD_TIME as above, but still the procedure is not being executed due to this trigger. How this trigger is affecting the procedures execution.
View 10 Replies
View Related
Aug 8, 2011
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 ..
View 7 Replies
View Related
Jul 7, 2011
I have a trigger which is calling a stored procedure that has PRAGMA AUTONOMOUS_TRANSACTION defined. The values that are passed from the trigger have been committed already but it appears that the values are not available in the stored procedure. I'm not positive of this since the ability to log/commit is difficult and the timing of the output is confusing me a bit. I'd like to know if it's expected that any passed values are simply available in the stored procedure regardless of the AUTONOMOUS_TRANSACTION?
View 18 Replies
View Related
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.
View 13 Replies
View Related
